package org.jeecgframework.web.service.impl;

import net.sf.json.JSONArray;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.Highchart;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.constant.Globals;
import org.jeecgframework.core.util.DateUtils;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.core.util.oConvertUtils;
import org.jeecgframework.web.service.CbsDelinerServiceI;
import org.jeecgframework.web.system.pojo.base.*;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.web.util.SendMailUtil;
import org.jeecgframework.web.util.SendMessage;
import org.jeecgframework.web.util.StaticValue;
import org.quartz.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.io.Serializable;
import java.util.Calendar;

@Service("cbsDelinerService")
@Transactional
public class CbsDelinerServiceImpl extends CommonServiceImpl implements CbsDelinerServiceI {

    @Autowired
    private SystemService systemService;

    public <T> void delete(T entity) {
        super.delete(entity);
        //执行删除操作配置的sql增强
        this.doDelSql((CbsDelinerEntity) entity);
    }

    public <T> Serializable save(T entity) {
        Serializable t = super.save(entity);
        //执行新增操作配置的sql增强
        this.doAddSql((CbsDelinerEntity) entity);
        return t;
    }

    public <T> void saveOrUpdate(T entity) {
        super.saveOrUpdate(entity);
        //执行更新操作配置的sql增强
        this.doUpdateSql((CbsDelinerEntity) entity);
    }

    /**
     * 默认按钮-sql增强-新增操作
     *
     * @param
     * @return
     */
    public boolean doAddSql(CbsDelinerEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-更新操作
     *
     * @param
     * @return
     */
    public boolean doUpdateSql(CbsDelinerEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-删除操作
     *
     * @param
     * @return
     */
    public boolean doDelSql(CbsDelinerEntity t) {
        return true;
    }


    /**
     * 发送邮件
     */
    public void sendMail(CbsDelinerEntity deliver, TSUser user, String type, String emailTitle, String ftl) {
        String code = "";  //发货单号
        String serviceCode = ""; //服务单号
        String fwsShortName = "";//服务商简称
        String enginnerName = "";//工程师姓名
        String enginnerPhoen = "";//工程师电话
        String CSAEmail = "";//值班CSA邮箱
        List<TSUser> zbCSAList = new ArrayList<>();//值班CSA
        String sendName = "";//CSA发货人姓名
        String sendPhone = "";//CSA发货人电话
        String wlEmail = "";//物流联系人邮箱
        List<ZyContactsEntity> wlContactsList = new ArrayList<>();
        String ckEmail = "";//仓库联系人邮箱
        String applyEmail = "";//申请人邮箱
        String applyName = "";//申请人姓名
        String applyPhone = "";//申请人电话
        String HDEmail = "";//审批人邮箱
        CbsEquipmentApplyEntity apply = null; //备件申请单

        String qxReason = "";//取消原因
        String qxUserName = "";//取消操作人
        String qxUserPhone = "";//取消操作人电话

        //获取所有值班CSA
        List<TSRoleUser> tsRoleUserList = systemService.findHql("from TSRoleUser where TSRole.roleCode='" + StaticValue.RULE_CODE_BARCO_CSA_ZB + "'");
        if (tsRoleUserList != null && tsRoleUserList.size() > 0) {
            for (TSRoleUser ru : tsRoleUserList) {
                if (ru != null && ru.getTSUser() != null && ru.getTSUser().getStatus() != null && ru.getTSUser().getStatus().equals(Globals.User_Normal)) {
                    zbCSAList.add(ru.getTSUser());
                }
            }
        }

        if (deliver != null) {
            qxReason = deliver.getOffReason();
            code = deliver.getCode();
            serviceCode = deliver.getCrCode();
            if (StringUtil.isNotEmpty(deliver.getEquipmentApplyId())) {
                apply = systemService.getEntity(CbsEquipmentApplyEntity.class, deliver.getEquipmentApplyId().getId());
            }
            if (StringUtil.isNotEmpty(apply)) {
                applyName = apply.getCreateName();
                if (StringUtil.isNotEmpty(apply.getCreateBy())) {
                    TSUser applyUser = super.singleResult("from TSUser where userName='" + apply.getCreateBy() + "'");
                    if (applyUser != null) {
                        applyEmail = applyUser.getEmail();
                        applyPhone = applyUser.getMobilePhone();
                    }
                }
                if (StringUtil.isNotEmpty(apply.getApproveUser())) {
                    TSUser HDUser = systemService.get(TSUser.class, apply.getApproveUser());
                    if (HDUser != null) {
                        HDEmail = HDUser.getEmail();
                    }
                }

            }

            //发货CSA
            if (deliver.getFhCSA() != null && StringUtil.isNotEmpty(deliver.getFhCSA().getId())) {
                TSUser fhCSA = super.get(TSUser.class, deliver.getFhCSA().getId());
                if (fhCSA != null) {
                    sendName = fhCSA.getRealName();
                    sendPhone = fhCSA.getMobilePhone();
                }
            }

            //取消人
            TSUser qxUser = deliver.getQxUser();
            if (qxUser != null) {
                qxUserName = qxUser.getRealName();
                qxUserPhone = qxUser.getMobilePhone();
            }

            //物流联系人
            TSDepart tsDepart = systemService.singleResult("from TSDepart where departname = '" + deliver.getDeliverLogistics() + "' and orgType='logistics' ");
            if (tsDepart != null) {
                wlContactsList = systemService.findHql("from ZyContactsEntity where belongOrg.id = '" + tsDepart.getId() + "' and state='" + StaticValue.DIC_ENGINEER_STATE_ONJOB + "'");
            }
            //仓库联系人
            if (deliver.getDeliverWarehouseId() != null && StringUtil.isNotEmpty(deliver.getDeliverWarehouseId().getId())) {
                CbsWarehouseEntity ware = super.get(CbsWarehouseEntity.class, deliver.getDeliverWarehouseId().getId());
                if (ware != null) {
                    ckEmail = ware.getEmail();
                }
            }

        }

        if (StringUtil.isNotEmpty(serviceCode)) {
            List<CbsServiceEntity> serviceList = super.findHql("from CbsServiceEntity where code='" + serviceCode + "'");
            if (serviceList != null && serviceList.size() > 0) {
                if (serviceList.get(0) != null) {
                    if (StringUtil.isNotEmpty(serviceList.get(0).getServiceProviderCode())) {
                        TSDepart fws = super.singleResult("from TSDepart where orgCode='" + serviceList.get(0).getServiceProviderCode() + "'");
                        if (fws != null) {
                            fwsShortName = fws.getShortname();
                        }
                    }

                    if (StringUtil.isNotEmpty(serviceList.get(0).getEngineer())) {
                        ZyEngineerEntity enginner = super.get(ZyEngineerEntity.class, serviceList.get(0).getEngineer());
                        if (enginner != null) {
                            enginnerName = enginner.getName();
                            enginnerPhoen = enginner.getCellphone();
                        }
                    }
                }
            }
        }


        Map<String, Object> paraMap1 = new HashMap<>();
        paraMap1.put("code", StringUtil.isNotEmpty(code) ? code : "");
        paraMap1.put("fwsShortName", StringUtil.isNotEmpty(fwsShortName) ? fwsShortName : "");
        paraMap1.put("enginnerName", StringUtil.isNotEmpty(enginnerName) ? enginnerName : "");
        paraMap1.put("enginnerPhoen", StringUtil.isNotEmpty(enginnerPhoen) ? enginnerPhoen : "");
        paraMap1.put("applyName", StringUtil.isNotEmpty(applyName) ? applyName : "");
        paraMap1.put("applyPhone", StringUtil.isNotEmpty(applyPhone) ? applyPhone : "");
        paraMap1.put("sendName", StringUtil.isNotEmpty(sendName) ? sendName : "");
        paraMap1.put("sendPhone", StringUtil.isNotEmpty(sendPhone) ? sendPhone : "");
        paraMap1.put("qxUserName", StringUtil.isNotEmpty(qxUserName) ? qxUserName : "");
        paraMap1.put("qxUserPhone", StringUtil.isNotEmpty(qxUserPhone) ? qxUserPhone : "");
        paraMap1.put("qxReason", StringUtil.isNotEmpty(qxReason) ? qxReason : "");

        if (type != null && !type.equals("")) {
            if (type.equals("CSA")) {//CSA
                if (zbCSAList != null && zbCSAList.size() > 0) {
                    for (TSUser CSAUser : zbCSAList) {
                        CSAEmail = CSAUser.getEmail();
                        if (StringUtil.isNotEmpty(CSAEmail)) {
                            ZyEmailEntity eMail = new ZyEmailEntity();
                            eMail.setEmailSubject(emailTitle);
                            eMail.setEmailReceiver(CSAEmail);
                            SendMailUtil.sendFtlMail(eMail, ftl, paraMap1, systemService);
                        }
                    }
                }
            }

            if (type.equals("wl")) {//物流
                if (wlContactsList != null && wlContactsList.size() > 0) {
                    for (ZyContactsEntity wlContacts : wlContactsList) {
                        if (wlContacts != null && StringUtil.isNotEmpty(wlContacts.getEmail())) {
                            wlEmail = wlContacts.getEmail();
                            ZyEmailEntity eMail = new ZyEmailEntity();
                            eMail.setEmailSubject(emailTitle);
                            eMail.setEmailReceiver(wlEmail);
                            SendMailUtil.sendFtlMail(eMail, ftl, paraMap1, systemService);
                        }
                    }

                }
            }

            if (type.equals("ck") && StringUtil.isNotEmpty(ckEmail)) {//仓库
                ZyEmailEntity eMail = new ZyEmailEntity();
                eMail.setEmailSubject(emailTitle);
                eMail.setEmailReceiver(ckEmail);
                SendMailUtil.sendFtlMail(eMail, ftl, paraMap1, systemService);
            }

            if (type.equals("sq") && StringUtil.isNotEmpty(applyEmail)) {//申请人
                ZyEmailEntity eMail = new ZyEmailEntity();
                eMail.setEmailSubject(emailTitle);
                eMail.setEmailReceiver(applyEmail);
                SendMailUtil.sendFtlMail(eMail, ftl, paraMap1, systemService);
            }

            if (type.equals("sp") && StringUtil.isNotEmpty(HDEmail)) {//审批人
                ZyEmailEntity eMail = new ZyEmailEntity();
                eMail.setEmailSubject(emailTitle);
                eMail.setEmailReceiver(HDEmail);
                SendMailUtil.sendFtlMail(eMail, ftl, paraMap1, systemService);
            }

        }

    }

    /**
     * 发送短信
     */
    public void sendMsg(CbsDelinerEntity deliver, TSUser user, String type, String ftl) {
        String code = "";  //发货单号
        String serviceCode = ""; //服务单号
        String enginnerPhoen = "";//工程师电话
        String sendName = "";//CSA发货人姓名
        String sendPhone = "";//CSA发货人电话
        String wlPhone = "";//物流联系人电话
        List<ZyContactsEntity> wlContactsList = new ArrayList<>();
        String ckPhone = "";//仓库联系人电话
        String applyPhone = "";//申请人电话
        String CSAPhone = "";//值班CSA电话
        List<TSUser> zbCSAList = new ArrayList<>();//值班CSA
        String signPhone = "";//收货人电话
        CbsEquipmentApplyEntity apply = null; //备件申请单


        //获取所有值班CSA
        List<TSRoleUser> tsRoleUserList = systemService.findHql("from TSRoleUser where TSRole.roleCode='" + StaticValue.RULE_CODE_BARCO_CSA_ZB + "'");
        if (tsRoleUserList != null && tsRoleUserList.size() > 0) {
            for (TSRoleUser ru : tsRoleUserList) {
                if (ru != null && ru.getTSUser() != null && ru.getTSUser().getStatus() != null && ru.getTSUser().getStatus().equals(Globals.User_Normal)) {
                    zbCSAList.add(ru.getTSUser());
                }
            }
        }

        if (deliver != null) {
            code = deliver.getCode();
            serviceCode = deliver.getCrCode();
            apply = deliver.getEquipmentApplyId();
            signPhone = deliver.getPhone();
            if (apply != null) {
                if (StringUtil.isNotEmpty(apply.getCreateBy())) {
                    TSUser applyUser = super.singleResult("from TSUser where userName='" + apply.getCreateBy() + "'");
                    if (applyUser != null) {
                        applyPhone = applyUser.getMobilePhone();
                    }
                }
            }

            //发货CSA
            if (deliver.getFhCSA() != null && StringUtil.isNotEmpty(deliver.getFhCSA().getId())) {
                TSUser fhCSA = super.get(TSUser.class, deliver.getFhCSA().getId());
                if (fhCSA != null) {
                    sendName = fhCSA.getRealName();
                    sendPhone = fhCSA.getMobilePhone();
                }
            }


            //物流联系人
            TSDepart tsDepart = systemService.singleResult("from TSDepart where departname = '" + deliver.getDeliverLogistics() + "' and orgType='logistics' ");
            if (tsDepart != null) {
                wlContactsList = systemService.findHql("from ZyContactsEntity where belongOrg.id = '" + tsDepart.getId() + "' and state='" + StaticValue.DIC_ENGINEER_STATE_ONJOB + "'");
            }
            //仓库联系人
            CbsWarehouseEntity ware = deliver.getDeliverWarehouseId();
            if (ware != null) {
                ckPhone = ware.getPhone();
            }

        }

        if (StringUtil.isNotEmpty(serviceCode)) {
            List<CbsServiceEntity> serviceList = super.findHql("from CbsServiceEntity where code='" + serviceCode + "'");
            if (serviceList != null && serviceList.size() > 0) {
                if (serviceList.get(0) != null) {
                    if (StringUtil.isNotEmpty(serviceList.get(0).getEngineer())) {
                        ZyEngineerEntity enginner = super.get(ZyEngineerEntity.class, serviceList.get(0).getEngineer());
                        if (enginner != null) {
                            enginnerPhoen = enginner.getCellphone();
                        }
                    }
                }
            }
        }


        Map<String, Object> paraMap = new HashMap<>();
        paraMap.put("code", code);
        paraMap.put("serviceCode", serviceCode);
        paraMap.put("sendName", sendName);
        paraMap.put("sendPhone", sendPhone);

        if (type != null && !type.equals("")) {
            if (type.equals("CSA")) {//CSA
                if (zbCSAList != null && zbCSAList.size() > 0) {
                    for (TSUser CSAUser : zbCSAList) {
                        CSAPhone = CSAUser.getMobilePhone();
                        if (StringUtil.isNotEmpty(CSAPhone)) {
                            SendMessage.sendSMSFTL(CSAPhone, ftl, paraMap, systemService);
                        }
                    }
                }

            }
            if (type.equals("wl")) {//物流
                if (wlContactsList != null && wlContactsList.size() > 0) {
                    for (ZyContactsEntity wlContacts : wlContactsList) {
                        if (wlContacts != null && StringUtil.isNotEmpty(wlContacts.getCellphone1())) {
                            wlPhone = wlContacts.getCellphone1();
                            SendMessage.sendSMSFTL(wlPhone, ftl, paraMap, systemService);
                        }
                    }

                }
            }
            if (type.equals("ck") && StringUtil.isNotEmpty(ckPhone)) {//仓库
                SendMessage.sendSMSFTL(ckPhone, ftl, paraMap, systemService);
            }
            if (type.equals("sq") && StringUtil.isNotEmpty(applyPhone)) {//申请人
                SendMessage.sendSMSFTL(applyPhone, ftl, paraMap, systemService);
            }
            if (type.equals("sh") && StringUtil.isNotEmpty(signPhone)) {//收货人
                SendMessage.sendSMSFTL(signPhone, ftl, paraMap, systemService);
            }
            if (type.equals("GCS") && StringUtil.isNotEmpty(enginnerPhoen)) {//服务单工程师
                SendMessage.sendSMSFTL(enginnerPhoen, ftl, paraMap, systemService);
            }

        }
    }

    /**
     * 替换sql中的变量
     *
     * @param sql
     * @return
     */
    public String replaceVal(String sql, CbsDelinerEntity t) {
        sql = sql.replace("#{id}", String.valueOf(t.getId()));
        sql = sql.replace("#{code}", String.valueOf(t.getCode()));
        sql = sql.replace("#{sr_code}", String.valueOf(t.getSrCode()));
        sql = sql.replace("#{so_code}", String.valueOf(t.getSoCode()));
        sql = sql.replace("#{cr_code}", String.valueOf(t.getCrCode()));
        sql = sql.replace("#{equipment_apply_id}", String.valueOf(t.getEquipmentApplyId()));
        sql = sql.replace("#{deliver_type}", String.valueOf(t.getDeliverType()));
        sql = sql.replace("#{zt}", String.valueOf(t.getZt()));
        sql = sql.replace("#{user_name}", String.valueOf(t.getUserName()));
        sql = sql.replace("#{phone}", String.valueOf(t.getPhone()));
        sql = sql.replace("#{cinema_name}", String.valueOf(t.getCinemaName()));
        sql = sql.replace("#{sheng}", String.valueOf(t.getSheng()));
        sql = sql.replace("#{shi}", String.valueOf(t.getShi()));
        sql = sql.replace("#{address}", String.valueOf(t.getAddress()));
        sql = sql.replace("#{warehouse_id}", String.valueOf(t.getWarehouseId()));
        sql = sql.replace("#{deliver_logistics}", String.valueOf(t.getDeliverLogistics()));
        sql = sql.replace("#{deliver_warehouse_id}", String.valueOf(t.getDeliverWarehouseId()));
        sql = sql.replace("#{deliver_des}", String.valueOf(t.getDeliverDes()));
        sql = sql.replace("#{status}", String.valueOf(t.getStatus()));
        sql = sql.replace("#{return_logistics}", String.valueOf(t.getReturnLogistics()));
        sql = sql.replace("#{create_name}", String.valueOf(t.getCreateName()));
        sql = sql.replace("#{create_by}", String.valueOf(t.getCreateBy()));
        sql = sql.replace("#{create_date}", String.valueOf(t.getCreateDate()));
        sql = sql.replace("#{update_name}", String.valueOf(t.getUpdateName()));
        sql = sql.replace("#{update_by}", String.valueOf(t.getUpdateBy()));
        sql = sql.replace("#{update_date}", String.valueOf(t.getUpdateDate()));
        sql = sql.replace("#{UUID}", UUID.randomUUID().toString());
        return sql;
    }


    /*
     * csa导出EXCEl
     * */
    public void exportDeliverList(HttpServletRequest request, HttpServletResponse response) {

        OutputStream out = null;
        try {
            String fileName = "发货单信息.xlsx";
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-msdownload;");
            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
            out = response.getOutputStream();
            String[] deliverIdsArr = oConvertUtils.getString(request.getParameter("deliverIds")).split(",");
            System.out.println("deliverIdsArr:" + deliverIdsArr.length);
            StringBuffer sql = new StringBuffer();
            String sql1 = "SELECT\n" +
                    "        ifnull(cd.id,'') AS  id,\n" +
                    "        ifnull(cd.code,'') AS code,\n" +
                    "        CASE WHEN cd. STATUS = '1' THEN 'CSA待发货' " +
                    "         WHEN cd. STATUS = '9' THEN 'CSA取消发货' " +
                    "         WHEN cd. STATUS = '4' THEN '欠货中' " +
                    "         WHEN cd. STATUS = '5' THEN '物流待发货' " +
                    "         WHEN cd. STATUS = '6' THEN '物流待签收' " +
                    "         WHEN cd. STATUS = '7' THEN '已签收' " +
                    "        END  AS status,\n" +
                    "        CASE WHEN cd. deliver_type = '1' THEN '备件申请' " +
                    "         WHEN cd. deliver_type = '2' THEN 'IMAX' " +
                    "         WHEN cd. deliver_type = '3' THEN '借用' " +
                    "         WHEN cd. deliver_type = '5' THEN '灯索赔' " +
                    "         WHEN cd. deliver_type = '6' THEN '备件新损' " +
                    "         WHEN cd. deliver_type = '7' THEN '其他' " +
                    "         WHEN cd. deliver_type = '4' THEN '零件采购' " +
                    "        END  AS deliverType,\n" +
                    "        ifnull(cd.so_code,'') AS soCode,\n" +
                    "        ifnull(cd.sr_code,'') AS srCode,\n" +
                    "        ifnull(cd.cinema_name,'') AS cinemaName,\n" +
                    "        ifnull(ts1.territoryname,'') AS shengName,\n" +
                    "        ifnull(ts2.territoryname,'') AS shiName,\n" +
                    "        CASE WHEN cd. FH_SLA = '1' THEN '普通' " +
                    "         WHEN cd. FH_SLA = '2' THEN '加急' " +
                    "         WHEN cd. FH_SLA = '3' THEN '当天到' " +
                    "        END  AS fhSLA,\n" +
                    "        ifnull(cde.type ,'') AS typeDesc,\n" +
                    "        ifnull(cde.item ,'') AS item,\n" +
                    "        ifnull(cde.total,'') AS total,\n" +
                    "        REPLACE(REPLACE(ifnull(cde.is_return,''),'2','是'),'1','否') AS isReturn,\n" +
                    "        ifnull(cd.space_number ,'')AS recordId,\n" +
                    "        ifnull(cd.cr_code,'') AS crCode,\n" +
                    "        ifnull(cd.deliver_logistics,'') AS deliverLogistics,\n" +
                    "        ifnull(cd.wl_SLA,'') AS wlSLA,\n" +
                    "        CASE WHEN cd. is_standard = '1' THEN 'Y' " +
                    "         WHEN cd. is_standard = '2' THEN 'N' " +
                    "         ELSE '--' " +
                    "        END  AS isStandard,\n" +
                    "        CASE WHEN cd. eta_standard = '1' THEN 'Y' " +
                    "         WHEN cd. eta_standard = '2' THEN 'N' " +
                    "         ELSE '--' " +
                    "        END  AS etaStandard,\n" +
                    "        CASE WHEN cd. sign_standard = '2' THEN 'Y' " +
                    "         WHEN cd. sign_standard = '1' THEN 'N' " +
                    "         ELSE '--' " +
                    "        END  AS signStandard,\n" +
                    "        ifnull(cd.create_date,'') AS createDate,\n" +
                    "        ifnull(cd.csa_sendtime,'') AS CSASendTime,\n" +
                    "        ifnull(cd.logistics_sendtime,'') AS logisticsSendTime,\n" +
                    "        ifnull(cd.expecttime,'') AS expectTime,\n" +
                    "        ifnull(cd.gettime,'') AS getTime,\n" +
                    "        ifnull(chl.create_date,'') AS getSysTime,\n" +
                    "        CASE WHEN cd. STATUS='7' THEN cd.logistics_duration  WHEN cd.status='6' THEN FORMAT(TIMESTAMPDIFF(SECOND,cd.logistics_sendtime, NOW())/60/60,2)  ELSE '--' END AS logisticsDuration,\n" +
                    "        ifnull(cde.logNumber,'') AS logNumber,\n" +
                    "        ifnull(cd.sign_method,'') AS sign_method,\n" +
                    "        ifnull(cd.sign_memo,'') AS sign_memo,\n" +
                    "        ifnull(cd.user_name,'') AS userName,\n" +
                    "        ifnull(cd.phone,'') AS phone,\n" +
                    "        ifnull(cd.address,'') AS address,\n" +
                    "        ifnull(cd.deliver_des,'') AS deliverDes,\n" +
                    "        ifnull(ts.realname,'') AS CSAName,\n" +
                    "        ifnull(cea.create_name,'无') AS applicant,\n" +
                    "        ifnull(tsd.shortname,'无') AS applicantOrg,\n" +
                    "        ifnull(deliversn,'') AS deliversn,\n" +
                    "        ifnull(deliverqr,'') AS deliverqr, \n" +
                    "        ifnull(kw,'') AS kw \n" +
                    "        FROM\n" +
                    "        cbs_deliver cd\n" +
                    "        LEFT JOIN (\n" +
                    "        SELECT\n" +
                    "        1 AS total,\n" +
                    "        type_des AS type,\n" +
                    "        deliver_id AS deliver_id,\n" +
                    "        equipment_apply_detail_id AS equipment_apply_detail_id,\n" +
                    "        apply_layer AS apply_layer,\n" +
                    "        item AS item,\n" +
                    "        is_return AS is_return,\n" +
                    "        ifnull(deliver_sn,'') AS deliversn,\n" +
                    "        ifnull(deliver_ercode,'') AS deliverqr,\n" +
                    "        REPLACE(REPLACE(ifnull(WAREHOUSE_POSITION,''),'1','库位1'),'2','库位2') AS kw,\n" +
                    "        logistics_number AS logNumber \n" +
                    "        FROM\n" +
                    "        `cbs_deliver_equipment`\n" +
                    "        ) cde ON cd.id = cde.deliver_id\n" +
                    "        LEFT JOIN t_s_base_user ts ON cd.fh_CSA=ts.id\n" +
                    "        LEFT JOIN t_s_territory ts1 ON ts1.ID=cd.sheng\n" +
                    "         LEFT JOIN t_s_territory ts2 ON ts2.ID=cd.shi\n" +
                    "        LEFT JOIN cbs_equipment_apply cea ON cea.record_id=cd.space_number\n" +
                    "        LEFT JOIN t_s_base_user tu ON cea.create_by=tu.username\n" +
                    "        LEFT JOIN t_s_user_org tso ON tso.user_id=tu.ID\n" +
                    "        LEFT JOIN t_s_depart tsd ON tsd.id=tso.org_id\n" +
                    "         LEFT JOIN cbs_handle_log chl ON chl.business_id=cd.id and chl.description like '%签收'\n" +
                    "        WHERE\n";
            sql.append(sql1);
            sql.append("	cd.id IN (\n");
            for (int i = 0; i < deliverIdsArr.length; i++) {
                if (i == deliverIdsArr.length - 1) {
                    sql.append("'" + deliverIdsArr[i] + "'");
                } else {
                    sql.append("'" + deliverIdsArr[i] + "',");
                }
            }
            sql.append(") ");
            sql.append(" ORDER BY cd.`status` ASC,cd.create_date DESC,cd.cinema_name ASC");

            System.out.println(sql);
            List<Map<String, Object>> reusltMaps = super.findForJdbc(sql.toString());
            InputStream is = ZyInspectionPlanServiceImpl.class.getClassLoader().getResourceAsStream("jeecg/template/deliverExcel.xlsx");
            XSSFWorkbook xssf = new XSSFWorkbook(is);
            XSSFSheet sheet = xssf.getSheetAt(0);
            int rowNum = 1;
            for (Map<String, Object> dataMap : reusltMaps) {
                XSSFRow row = sheet.createRow(rowNum);
                int cellNum = 0, index = 0;
                for (String key : dataMap.keySet()) {
                    if (index == 0) {
                        index++;
                        continue;
                    }
                    XSSFCell cell = row.createCell(cellNum);
                    cell.setCellValue(dataMap.get(key) != null ? dataMap.get(key).toString() : "");
                    cellNum++;
                }
                rowNum++;
            }
            xssf.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /*
     * 物流导出EXCEl
     * */
    public void exportDeliverListForLogistics(HttpServletRequest request, HttpServletResponse response) {

        OutputStream out = null;
        try {
            String fileName = "发货单信息.xlsx";
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-msdownload;");
            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
            out = response.getOutputStream();
            String[] deliverIdsArr = oConvertUtils.getString(request.getParameter("deliverIds")).split(",");
            System.out.println("deliverIdsArr:" + deliverIdsArr.length);
            StringBuffer sql = new StringBuffer();
            String sql1 = "SELECT\n" +
                    "        ifnull(cd.id,'') AS  id,\n" +
                    "        ifnull(cd.code,'') AS code,\n" +
                    "        ifnull(cd.cr_code,'') AS crCode,\n" +
                    "        ifnull(cd.so_code,'') AS soCode,\n" +
                    "        ifnull(cd.sr_code,'') AS srCode,\n" +
                    "        ifnull(cd.cinema_name,'') AS cinemaName,\n" +
                    "        ifnull(ts1.territoryname,'') AS shengName,\n" +
                    "        ifnull(ts2.territoryname,'') AS shiName,\n" +
                    "        ifnull(GROUP_CONCAT(cde.type SEPARATOR '\\\\'),'') AS typeDesc,\n" +
                    "        ifnull(GROUP_CONCAT(cde.total SEPARATOR '\\\\'),'') AS total,\n" +
                    "        CASE WHEN cd. FH_SLA = '1' THEN '普通' " +
                    "         WHEN cd. FH_SLA = '2' THEN '加急' " +
                    "         WHEN cd. FH_SLA = '3' THEN '当天到' " +
                    "        END  AS fhSLA,\n" +
                    "        ifnull(cd.csa_sendtime,'') AS CSASendTime,\n" +
                    "        ifnull(cd.logistics_sendtime,'') AS logisticsSendTime,\n" +
                    "        ifnull(cd.gettime,'') AS getTime,\n" +
                    "        CASE WHEN cd.is_standard = '1' THEN 'Y' " +
                    "         WHEN cd. is_standard = '2' THEN 'N' " +
                    "         WHEN cd. is_standard not in ('1','2') THEN '' " +
                    "         WHEN cd. is_standard IS NULL THEN '' " +
                    "        END  AS isStandard,\n" +
                    "        CASE WHEN cd.eta_standard = '1' THEN 'Y' " +
                    "         WHEN cd. eta_standard = '2' THEN 'N' " +
                    "         WHEN cd. eta_standard not in ('1','2') THEN '' " +
                    "         WHEN cd. eta_standard IS NULL THEN '' " +
                    "        END  AS etaStandard,\n" +
                    "        CASE WHEN cd.sign_standard = '1' THEN 'Y' " +
                    "         WHEN cd. sign_standard = '2' THEN 'N' " +
                    "         WHEN cd. sign_standard not in ('1','2') THEN '' " +
                    "         WHEN cd. sign_standard IS NULL THEN '' " +
                    "        END  AS signStandard,\n" +
                    "        CASE WHEN cd. STATUS = '1' THEN 'CSA待发货' " +
                    "         WHEN cd. STATUS = '9' THEN 'CSA取消发货' " +
                    "         WHEN cd. STATUS = '4' THEN '欠货中' " +
                    "         WHEN cd. STATUS = '5' THEN '物流待发货' " +
                    "         WHEN cd. STATUS = '6' THEN '物流待签收' " +
                    "         WHEN cd. STATUS = '7' THEN '已签收' " +
                    "        END  AS status,\n" +
                    "        ifnull(GROUP_CONCAT(DISTINCT cde.logNumber SEPARATOR '\\,'),'') AS logNumber,\n" +
                    "        ifnull(ts.realname,'') AS CSAName,\n" +
                    "        ifnull(cd.eta_qunqualified,'') AS etanot_qualified,\n" +
                    "        ifnull(cd.eta_not_qualified_memo,'') AS etanot_qualified_memo,\n" +
                    "        ifnull(cd.not_qualified,'') AS not_qualified,\n" +
                    "        ifnull(cd.not_qualified_memo,'') AS not_qualified_memo,\n" +
                    "        ifnull(cd.user_name,'') AS receiver,\n" +
                    "        ifnull(cd.phone,'') AS phone,\n" +
                    "        ifnull(cd.address,'') AS address \n" +
                    "        FROM\n" +
                    "        cbs_deliver cd\n" +
                    "        LEFT JOIN (\n" +
                    "        SELECT\n" +
                    "        COUNT(type_id) AS total,\n" +
                    "        type_des AS type,\n" +
                    "        deliver_id AS deliver_id,\n" +
                    "        equipment_apply_detail_id AS equipment_apply_detail_id,\n" +
                    "        apply_layer AS apply_layer,\n" +
                    "        logistics_number AS logNumber\n" +
                    "        FROM\n" +
                    "        `cbs_deliver_equipment`\n" +
                    "        GROUP BY\n" +
                    "        deliver_id,type_id\n" +
                    "        ) cde ON cd.id = cde.deliver_id\n" +
                    "        LEFT JOIN t_s_base_user ts ON cd.fh_CSA=ts.id\n" +
                    "        LEFT JOIN t_s_territory ts1 ON ts1.ID=cd.sheng\n" +
                    "         LEFT JOIN t_s_territory ts2 ON ts2.ID=cd.shi\n" +
                    "        WHERE\n";
            sql.append(sql1);
            sql.append("	cd.id IN (\n");
            for (int i = 0; i < deliverIdsArr.length; i++) {
                if (i == deliverIdsArr.length - 1) {
                    sql.append("'" + deliverIdsArr[i] + "'");
                } else {
                    sql.append("'" + deliverIdsArr[i] + "',");
                }
            }
            sql.append(") GROUP BY cd.code");

            System.out.println(sql);
            List<Map<String, Object>> reusltMaps = super.findForJdbc(sql.toString());
            InputStream is = ZyInspectionPlanServiceImpl.class.getClassLoader().getResourceAsStream("jeecg/template/deliverListForLogistics.xlsx");
            XSSFWorkbook xssf = new XSSFWorkbook(is);
            XSSFSheet sheet = xssf.getSheetAt(0);
            int rowNum = 1;
            for (Map<String, Object> dataMap : reusltMaps) {
                XSSFRow row = sheet.createRow(rowNum);
                int cellNum = 0, index = 0;
                for (String key : dataMap.keySet()) {
                    if (index == 0) {
                        index++;
                        continue;
                    }
                    XSSFCell cell = row.createCell(cellNum);
                    cell.setCellValue(dataMap.get(key) != null ? dataMap.get(key).toString() : "");
                    cellNum++;
                }
                rowNum++;
            }
            xssf.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Map<String, Object> ifQualifiedLevel(String minTime, String maxTime, String slaType) throws ParseException {
        Map<String, Object> map = new HashMap();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH");
        Date newDate = sdf.parse(maxTime);
        Date oldDate = sdf.parse(minTime);
        long time = newDate.getTime();
        long oldtime = oldDate.getTime();
        long diff = 1;
        // 如果发货SLA为普通 ,则预计到货时间-CSA派单时间-24h
        if (slaType != null) {
            if ("pt".equals(slaType)) {
                diff = time - oldtime - 86400000;
            }
        } else {
            diff = time - oldtime;
        }
        if (diff <= 0) {
            diff = 1;
        }
        double hours = diff / 1000 / 60 / 60.0;
        DecimalFormat df = new DecimalFormat("##.#");
        String dff = df.format(hours);

        if (Double.valueOf(dff) <= 24) {
            map.put("status", 1);
        }
        if (Double.valueOf(dff) > 24 && Double.valueOf(dff) <= 48) {
            map.put("status", 2);
        }
        if (Double.valueOf(dff) > 48 && Double.valueOf(dff) <= 72) {
            map.put("status", 3);
        }
        if (Double.valueOf(dff) > 72) {
            map.put("status", 4);
        }
        return map;
    }

    @Override
    public void addDeliverLog(String description, String businessId, int processStatus) {
        TSUser user = ResourceUtil.getSessionUserName();
        CbsHandleLogEntity log = new CbsHandleLogEntity();
        log.setBusinessId(businessId);
        log.setDescription(description);
        log.setLogType(2);
        log.setCreateDate(new Date());
        log.setCreateName(user.getRealName());
        log.setProcessStatus(processStatus);
        systemService.save(log);
    }

    public void taskTimeTest(Scheduler sched, String triggerName, String triggerGroupName, String time) throws SchedulerException {
        CronTrigger trigger = (CronTrigger) sched.getTrigger("timeTaskOfSendMailTrigger", "timeTaskOfSendMail");
        if (trigger == null) {
            return;
        }
        String oldTime = trigger.getCronExpression();
        if (!oldTime.equalsIgnoreCase(time)) {
            //
            JobDetail jobDetail = sched.getJobDetail(triggerName, "timeTaskOfSendMail");
            Class objJobClass = jobDetail.getJobClass();
            removeJob(sched, "timeTaskOfSendMailTrigger");
            addJob(sched, "timeTaskOfSendMailTrigger", objJobClass, time);
        }
    }

    public static void removeJob(Scheduler sched, String jobName) {
        try {
            sched.pauseTrigger(jobName, "schedulerFactoryForWarrantyPeriod");// 停止触发器
            sched.unscheduleJob(jobName, "schedulerFactoryForWarrantyPeriod");// 移除触发器
            sched.deleteJob(jobName, "timeTaskOfSendMailTrigger");// 删除任务
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static void addJob(Scheduler sched, String jobName, @SuppressWarnings("rawtypes") Class cls, String time) {
        try {
            JobDetail jobDetail = new JobDetail(jobName, "schedulerFactoryForWarrantyPeriod", cls);// 任务名，任务组，任务执行类
            // 触发器
            CronTrigger trigger = new CronTrigger(jobName, "timeTaskOfSendMailTrigger");// 触发器名,触发器组
            trigger.setCronExpression(time);// 触发器时间设定
            sched.scheduleJob(jobDetail, trigger);
            // 启动
            if (!sched.isShutdown()) {
                sched.start();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void getDeliverIsDfh() {
    	// add start 190716 Amy 9点30之前和23点之后不提醒
    	Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date());
        int hour = calendar.get(Calendar.HOUR_OF_DAY);
        int minutes = calendar.get(Calendar.MINUTE);
    	if (hour < 9 || hour >= 23 || (hour == 9 && minutes < 30)) {
    		return;
    	}
    	// add end 90716 Amy 9点30之前和23点之后不提醒
        List<CbsDelinerEntity> cbsDelinerEntityList = systemService.findHql("from CbsDelinerEntity where status = " + StaticValue.wldfh + "");
        if (StringUtil.isNotEmpty(cbsDelinerEntityList)) {
            if (cbsDelinerEntityList.size() > 0) {
                for (int i = 0; i < cbsDelinerEntityList.size(); i++) {
                    if (StringUtil.isNotEmpty(cbsDelinerEntityList.get(i).getCSASendTime())) {
                        Date csaSendTime = cbsDelinerEntityList.get(i).getCSASendTime();
                        Date time = new Date();
                        long interval = (time.getTime() - csaSendTime.getTime());
                        System.out.println("相差---" + interval);
                        if (interval >= 7200000) {
                            StringBuffer stringBuffer = new StringBuffer();
                            Map<String, Object> objectMap = new HashMap<>();
                            stringBuffer.append("物流发货超时提醒：<" + cbsDelinerEntityList.get(i).getCode() + ">");
                            objectMap.put("code", cbsDelinerEntityList.get(i).getCode());
                            if (cbsDelinerEntityList.get(i).getCrCode() != null) {
                                stringBuffer.append("<" + cbsDelinerEntityList.get(i).getCrCode() + ">--");
                            }
                            if (cbsDelinerEntityList.get(i).getCinemaName() != null) {
                                stringBuffer.append("<" + cbsDelinerEntityList.get(i).getCinemaName() + ">-");
                            }
                            SimpleDateFormat sdf = new SimpleDateFormat("MMdd");
                            Date date = new Date();
                            String time1 = sdf.format(date);
                            stringBuffer.append(time1);
                            TSUser tsUser = ResourceUtil.getSessionUserName();
                            if (StringUtil.isNotEmpty(cbsDelinerEntityList.get(i).getIsSend())) {
                                if (cbsDelinerEntityList.get(i).getIsSend() != 1) {
                                    sendMsg(cbsDelinerEntityList.get(i), tsUser, "wl", "message_send_timeTask_sms.ftl");
                                    sendMsg(cbsDelinerEntityList.get(i), tsUser, "ck", "message_send_timeTask_sms.ftl");
                                    sendMsg(cbsDelinerEntityList.get(i), tsUser, "CSA", "message_send_timeTask_sms.ftl");
                                    sendMail(cbsDelinerEntityList.get(i), tsUser, "wl", stringBuffer.toString(), "message_send_timeTask_email.ftl");
                                    sendMail(cbsDelinerEntityList.get(i), tsUser, "CSA", stringBuffer.toString(), "message_send_timeTask_email.ftl");
                                    sendMail(cbsDelinerEntityList.get(i), tsUser, "ck", stringBuffer.toString(), "message_send_timeTask_email.ftl");
                                    systemService.updateBySqlString("update cbs_deliver set is_send = 1 where id = '" + cbsDelinerEntityList.get(i).getId() + "'");
                                }
                            } else {
                                sendMsg(cbsDelinerEntityList.get(i), tsUser, "wl", "message_send_timeTask_sms.ftl");
                                sendMsg(cbsDelinerEntityList.get(i), tsUser, "ck", "message_send_timeTask_sms.ftl");
                                sendMsg(cbsDelinerEntityList.get(i), tsUser, "CSA", "message_send_timeTask_sms.ftl");
                                sendMail(cbsDelinerEntityList.get(i), tsUser, "wl", stringBuffer.toString(), "message_send_timeTask_email.ftl");
                                sendMail(cbsDelinerEntityList.get(i), tsUser, "CSA", stringBuffer.toString(), "message_send_timeTask_email.ftl");
                                sendMail(cbsDelinerEntityList.get(i), tsUser, "ck", stringBuffer.toString(), "message_send_timeTask_email.ftl");
                                systemService.updateBySqlString("update cbs_deliver set is_send = 1 where id = '" + cbsDelinerEntityList.get(i).getId() + "'");
                            }

                        }
                    }

                }
            }
        }
    }

    @Override
    public List<Map<String, Object>> getDeliverAging(String deliverLogistics, String sendType, String month, String week, String startTime, String endTime) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        List<Map<String, Object>> deliverAgingList = null;
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT\n" +
                "\tSUM(\n" +
                "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 24\n" +
                "\t) AS lessOrEqTwf,\n" +
                "\tSUM(\n" +
                "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 24\n" +
                "\t\tAND FORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 48\n" +
                "\t) AS lessOrEqFoEg,\n" +
                "\tSUM(\n" +
                "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 48\n" +
                "\t\tAND FORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 72\n" +
                "\t) AS lessOrEqSenTw,SUM(\n" +
                "\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 72\n" +
                "\t) as biggerOrEqSenTw,\n" +
                "\tSUM(`status` != 7 AND `status` != 9) AS waying,\n" +
                "\tSUM(`status` != 9) AS total,deliver_logistics as logistics\n" +
                "FROM\n" +
                "\tcbs_deliver where 1=1 ");
        if (StringUtil.isNotEmpty(deliverLogistics)) {
            stringBuffer.append(" and deliver_logistics = '" + deliverLogistics + "'");
            if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                stringBuffer.append("AND logistics_sendtime BETWEEN '" + startTime + "' and '" + endTime + "'");
            }
            if (StringUtil.isNotEmpty(month)) {
                SimpleDateFormat sdf = new SimpleDateFormat("YYYY");
                String time = sdf.format(new Date());
                String nowMonth = time + "-" + month;
                stringBuffer.append("AND date_format(create_date,'%Y-%m')='" + nowMonth + "'");
            }
            if (StringUtil.isNotEmpty(sendType)) {
                stringBuffer.append("AND deliver_type = '" + sendType + "'");
            }
            if (StringUtil.isNotEmpty(week)) {
                stringBuffer.append(" and WEEK(date_add(create_date,interval 6 day),2)  = " + week + "");
            }
            deliverAgingList = super.findForJdbc(stringBuffer.toString());
            for (Map<String, Object> map : deliverAgingList) {
                Map rsMap = new HashMap();
                if (map.get("lessOrEqTwf") == null) {
                    rsMap.put("lessOrEqTwf", "0");
                } else {
                    rsMap.put("lessOrEqTwf", map.get("lessOrEqTwf").toString());
                }
                if (map.get("lessOrEqFoEg") == null) {
                    rsMap.put("lessOrEqFoEg", "0");
                } else {
                    rsMap.put("lessOrEqFoEg", map.get("lessOrEqFoEg").toString());
                }
                if (map.get("lessOrEqSenTw") == null) {
                    rsMap.put("lessOrEqSenTw", "0");
                } else {
                    rsMap.put("lessOrEqSenTw", map.get("lessOrEqSenTw").toString());
                }
                if (map.get("biggerOrEqSenTw") == null) {
                    rsMap.put("biggerOrEqSenTw", "0");
                } else {
                    rsMap.put("biggerOrEqSenTw", map.get("biggerOrEqSenTw").toString());
                }
                if (map.get("waying") == null) {
                    rsMap.put("waying", "0");
                } else {
                    rsMap.put("waying", map.get("waying").toString());
                }
                if (map.get("logistics") == null) {
                    rsMap.put("logistics", deliverLogistics);
                } else {
                    rsMap.put("logistics", map.get("logistics").toString());
                }
                if (map.get("total") == null) {
                    rsMap.put("total", "0");
                } else {
                    rsMap.put("total", map.get("total").toString());
                }
                dataList.add(rsMap);
            }
        } else {
            List<TSDepart> tsDepartList = systemService.findHql("from TSDepart where orgType ='logistics'");
            if (StringUtil.isNotEmpty(tsDepartList)) {
                for (int i = 0; i < tsDepartList.size(); i++) {
                    StringBuffer sb = new StringBuffer();
                    sb.append("SELECT\n" +
                            "\tSUM(\n" +
                            "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 24\n" +
                            "\t) AS lessOrEqTwf,\n" +
                            "\tSUM(\n" +
                            "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 24\n" +
                            "\t\tAND FORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 48\n" +
                            "\t) AS lessOrEqFoEg,\n" +
                            "\tSUM(\n" +
                            "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 48\n" +
                            "\t\tAND FORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 72\n" +
                            "\t) AS lessOrEqSenTw,SUM(\n" +
                            "\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 72\n" +
                            "\t) as biggerOrEqSenTw,\n" +
                            "\tSUM(`status` != 7 AND `status` != 9) AS waying,\n" +
                            "\tSUM(`status` != 9) AS total,deliver_logistics as logistics\n" +
                            "FROM\n" +
                            "\tcbs_deliver where 1=1 and deliver_logistics = '" + tsDepartList.get(i).getDepartname() + "'");
                    if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                        sb.append("AND logistics_sendtime BETWEEN '" + startTime + "' and '" + endTime + "'");
                    }
                    if (StringUtil.isNotEmpty(month)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("YYYY");
                        String time = sdf.format(new Date());
                        String nowMonth = time + "-" + month;
                        sb.append("AND date_format(create_date,'%Y-%m')='" + nowMonth + "'");
                    }
                    if (StringUtil.isNotEmpty(week)) {
                        sb.append(" and WEEK(date_add(create_date,interval 6 day),2)  = " + week + "");
                    }
                    if (StringUtil.isNotEmpty(sendType)) {
                        sb.append("AND deliver_type = '" + sendType + "'");
                    }
                    deliverAgingList = super.findForJdbc(sb.toString());
                    if (StringUtil.isNotEmpty(deliverAgingList)) {
                        for (Map<String, Object> map : deliverAgingList) {
                            Map rsMap = new HashMap();
                            if (map.get("lessOrEqTwf") == null) {
                                rsMap.put("lessOrEqTwf", "0");
                            } else {
                                rsMap.put("lessOrEqTwf", map.get("lessOrEqTwf").toString());
                            }
                            if (map.get("lessOrEqFoEg") == null) {
                                rsMap.put("lessOrEqFoEg", "0");
                            } else {
                                rsMap.put("lessOrEqFoEg", map.get("lessOrEqFoEg").toString());
                            }
                            if (map.get("lessOrEqSenTw") == null) {
                                rsMap.put("lessOrEqSenTw", "0");
                            } else {
                                rsMap.put("lessOrEqSenTw", map.get("lessOrEqSenTw").toString());
                            }
                            if (map.get("biggerOrEqSenTw") == null) {
                                rsMap.put("biggerOrEqSenTw", "0");
                            } else {
                                rsMap.put("biggerOrEqSenTw", map.get("biggerOrEqSenTw").toString());
                            }
                            if (map.get("waying") == null) {
                                rsMap.put("waying", "0");
                            } else {
                                rsMap.put("waying", map.get("waying").toString());
                            }
                            if (map.get("logistics") == null) {
                                rsMap.put("logistics", tsDepartList.get(i).getDepartname());
                            } else {
                                rsMap.put("logistics", map.get("logistics").toString());
                            }
                            if (map.get("total") == null) {
                                rsMap.put("total", "0");
                            } else {
                                rsMap.put("total", map.get("total").toString());
                            }
                            dataList.add(rsMap);
                        }
                    }
                }
            }
        }

        return dataList;
    }

    @Override
    public List<Highchart> getCharData(String deliverLogistics, String sendType, String month, String week, String startTime, String endTime) {
        List<Highchart> rsList = new ArrayList<Highchart>();
        if (StringUtil.isNotEmpty(deliverLogistics)) {
            List<Map<String, Object>> deliverAgingList = null;
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("SELECT\n" +
                    "\tSUM(\n" +
                    "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 24\n" +
                    "\t) AS lessOrEqTwf,\n" +
                    "\tSUM(\n" +
                    "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 24\n" +
                    "\t\tAND FORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 48\n" +
                    "\t) AS lessOrEqFoEg,\n" +
                    "\tSUM(\n" +
                    "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 48\n" +
                    "\t\tAND FORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 72\n" +
                    "\t) AS lessOrEqSenTw,SUM(\n" +
                    "\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 72\n" +
                    "\t) as biggerOrEqSenTw,\n" +
                    "\tSUM(`status` != 7 AND `status` != 9) AS waying,\n" +
                    "\tSUM(`status` != 9) AS total,deliver_logistics as logistics\n" +
                    "FROM\n" +
                    "\tcbs_deliver where 1=1 and deliver_logistics = '" + deliverLogistics + "'");
            if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                stringBuffer.append("AND logistics_sendtime BETWEEN '" + startTime + "' and '" + endTime + "'");
            }
            if (StringUtil.isNotEmpty(month)) {
                SimpleDateFormat sdf = new SimpleDateFormat("YYYY");
                String time = sdf.format(new Date());
                String nowMonth = time + "-" + month;
                stringBuffer.append("AND date_format(create_date,'%Y-%m')='" + nowMonth + "'");
            }
            if (StringUtil.isNotEmpty(sendType)) {
                stringBuffer.append("AND deliver_type = '" + sendType + "'");
            }
            if (StringUtil.isNotEmpty(week)) {
                stringBuffer.append(" and WEEK(date_add(create_date,interval 6 day),2)  = " + week + "");
            }
            deliverAgingList = super.findForJdbc(stringBuffer.toString());
            List<Integer> lessTwenty = new ArrayList<Integer>();
            for (Map<String, Object> map : deliverAgingList) {
                if (map.get("total") == null) {
                    lessTwenty.add(0);
                } else {
                    lessTwenty.add(Integer.valueOf(map.get("total").toString()));
                }
                if (map.get("lessOrEqTwf") == null) {
                    lessTwenty.add(Integer.valueOf(0));
                } else {
                    lessTwenty.add(Integer.valueOf(map.get("lessOrEqTwf").toString()));

                }
                if (map.get("lessOrEqFoEg") == null) {
                    lessTwenty.add(Integer.valueOf(0));
                } else {
                    lessTwenty.add(Integer.valueOf(map.get("lessOrEqFoEg").toString()));

                }
                if (map.get("lessOrEqSenTw") == null) {
                    lessTwenty.add(Integer.valueOf(0));
                } else {

                    lessTwenty.add(Integer.valueOf(map.get("lessOrEqSenTw").toString()));
                }
                if (map.get("biggerOrEqSenTw") == null) {
                    lessTwenty.add(0);
                } else {
                    lessTwenty.add(Integer.valueOf(map.get("biggerOrEqSenTw").toString()));
                }
                if (map.get("waying") == null) {
                    lessTwenty.add(0);
                } else {

                    lessTwenty.add(Integer.valueOf(map.get("waying").toString()));
                }
                Highchart alreadySendHc = new Highchart();
                alreadySendHc.setName(deliverLogistics);
                alreadySendHc.setData(lessTwenty);
                rsList.add(alreadySendHc);
            }
        } else {
            List<TSDepart> tsDepartList = systemService.findHql("from TSDepart where orgType ='logistics'");
            if (StringUtil.isNotEmpty(tsDepartList)) {
                for (int i = 0; i < tsDepartList.size(); i++) {
                    StringBuffer sb = new StringBuffer();
                    sb.append("SELECT\n" +
                            "\tSUM(\n" +
                            "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 24\n" +
                            "\t) AS lessOrEqTwf,\n" +
                            "\tSUM(\n" +
                            "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 24\n" +
                            "\t\tAND FORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 48\n" +
                            "\t) AS lessOrEqFoEg,\n" +
                            "\tSUM(\n" +
                            "\t\tFORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 48\n" +
                            "\t\tAND FORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) <= 72\n" +
                            "\t) AS lessOrEqSenTw,SUM(\n" +
                            "\t FORMAT(TIMESTAMPDIFF(SECOND,logistics_sendtime, gettime)/60/60,2) > 72\n" +
                            "\t) as biggerOrEqSenTw,\n" +
                            "\tSUM(`status` != 7 AND `status` != 9) AS waying,\n" +
                            "\tSUM(`status` != 9) AS total,deliver_logistics as logistics\n" +
                            "FROM\n" +
                            "\tcbs_deliver where 1=1 and deliver_logistics = '" + tsDepartList.get(i).getDepartname() + "'");
                    if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                        sb.append("AND logistics_sendtime BETWEEN '" + startTime + "' and '" + endTime + "'");
                    }
                    if (StringUtil.isNotEmpty(month)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("YYYY");
                        String time = sdf.format(new Date());
                        String nowMonth = time + "-" + month;
                        sb.append("AND date_format(create_date,'%Y-%m')='" + nowMonth + "'");
                    }
                    if (StringUtil.isNotEmpty(sendType)) {
                        sb.append("AND deliver_type = '" + sendType + "'");
                    }
                    if (StringUtil.isNotEmpty(week)) {
                        sb.append(" and WEEK(date_add(create_date,interval 6 day),2)  = " + week + "");
                    }
                    List<Map<String, Object>> deliverAgingList = super.findForJdbc(sb.toString());

                    List<Integer> lessTwenty = new ArrayList<Integer>();
                    for (Map<String, Object> map : deliverAgingList) {
                        if (map.get("total") == null) {
                            lessTwenty.add(0);
                        } else {
                            lessTwenty.add(Integer.valueOf(map.get("total").toString()));
                        }
                        if (map.get("lessOrEqTwf") == null) {
                            lessTwenty.add(Integer.valueOf(0));
                        } else {
                            lessTwenty.add(Integer.valueOf(map.get("lessOrEqTwf").toString()));

                        }
                        if (map.get("lessOrEqFoEg") == null) {
                            lessTwenty.add(Integer.valueOf(0));
                        } else {
                            lessTwenty.add(Integer.valueOf(map.get("lessOrEqFoEg").toString()));

                        }
                        if (map.get("lessOrEqSenTw") == null) {
                            lessTwenty.add(Integer.valueOf(0));
                        } else {

                            lessTwenty.add(Integer.valueOf(map.get("lessOrEqSenTw").toString()));
                        }
                        if (map.get("biggerOrEqSenTw") == null) {
                            lessTwenty.add(0);
                        } else {
                            lessTwenty.add(Integer.valueOf(map.get("biggerOrEqSenTw").toString()));
                        }
                        if (map.get("waying") == null) {
                            lessTwenty.add(0);
                        } else {

                            lessTwenty.add(Integer.valueOf(map.get("waying").toString()));
                        }
                        Highchart alreadySendHc = new Highchart();
                        alreadySendHc.setName(tsDepartList.get(i).getDepartname());
                        alreadySendHc.setData(lessTwenty);
                        rsList.add(alreadySendHc);
                    }
                }

            }
        }
        System.out.println(rsList);
        return rsList;
    }

    /*Gross*/
    @Override
    public List<Highchart> toStatisticalShipment(String name, String slaType, String type, String yue, String week, String startTime, String endTime, boolean b) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("YYYY");
        String nowYear = sdf.format(new Date());
        String JanMon = nowYear + "-01";
        String DecMon = nowYear + "-12";
        List<String> monthBetween = getMonthBetween(JanMon, DecMon);
        List<Highchart> rsList = new ArrayList<>();
        DecimalFormat df = new DecimalFormat("0.0000");

        List<Map<String, Double>> azFaultRate = new ArrayList<>();
        if (b) {

            String nowMon = null;
            Integer minNumber = 0;
            Integer maxNumber = 0;
            StringBuffer sb = new StringBuffer();
            sb.append("select gettime,csa_sendtime,not_qualified,eta_qunqualified from cbs_deliver lesson where 1 = 1");
            if (StringUtil.isNotEmpty(yue)) {
                nowMon = nowYear + "-" + yue;
                sb.append(" and date_format(csa_sendtime,'%Y-%m')='" + nowMon + "'");
            }
            if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                sb.append(" AND csa_sendtime BETWEEN '" + startTime + "' and '" + endTime + "'");
            }
            if (StringUtil.isNotEmpty(week)) {
                sb.append(" and WEEK(date_add(csa_sendtime,interval 6 day),2)  = " + week + "");
            }
            sb.append(" and deliver_logistics = '" + name + "' AND `status` = 7");
            List<Map<String, Object>> cbsDeliverMapList = systemService.findForJdbc(sb.toString());
            //实际时效为SL1
            List<Map<String, Object>> SL1List = new ArrayList<>();
            List<Map<String, Object>> SL2List = new ArrayList<>();
            for (Map<String, Object> map : cbsDeliverMapList) {
                maxNumber++;
                Integer integer = statisticalShipment(map.get("gettime").toString(), map.get("csa_sendtime").toString());
                if (integer == 1) {
                    SL1List.add(map);
                    SL2List.add(map);
                    minNumber++;
                }
                if (integer == 2) {
                    SL2List.add(map);

                }

            }
            double faultRate = 0;
            if (maxNumber != 0) {
                String faultRateStr = null;
                if ("SL2".equals(slaType)) {
                    faultRateStr = df.format(Double.valueOf(SL2List.size()) / Double.valueOf(cbsDeliverMapList.size()));
                } else {
                    faultRateStr = df.format(Double.valueOf(SL1List.size()) / Double.valueOf(cbsDeliverMapList.size()));

                }
                faultRate = Double.valueOf(faultRateStr);
            }
            if ("Target".equals(name)) {
                Map<String, Double> azMap = new HashMap();
                if ("SL1".equals(slaType)) {
                    if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                        azMap.put("" + startTime + "-" + endTime + "", 0.75);
                    } else if (StringUtil.isNotEmpty(week)) {
                        azMap.put("第" + week + "周", 0.75);
                    } else {
                        azMap.put(changeDate(nowMon), 0.75);
                    }
                } else {
                    if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                        azMap.put("" + startTime + "-" + endTime + "", 0.9);
                    } else if (StringUtil.isNotEmpty(week)) {
                        azMap.put("第" + week + "周", 0.9);
                    } else {
                        azMap.put(changeDate(nowMon), 0.9);
                    }
                }
                azFaultRate.add(azMap);
            } else if ("Combine".equals(name)) {
                StringBuffer stringBuffer = new StringBuffer();
                stringBuffer.append("select gettime,csa_sendtime from cbs_deliver lesson where 1=1  and status = 7 ");
                if (StringUtil.isNotEmpty(yue)) {
                    nowMon = nowYear + "-" + yue;
                    stringBuffer.append(" and date_format(csa_sendtime,'%Y-%m')='" + nowMon + "'");
                }
                if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                    stringBuffer.append(" AND csa_sendtime BETWEEN '" + startTime + "' and '" + endTime + "'");
                }
                if (StringUtil.isNotEmpty(week)) {
                    stringBuffer.append(" and WEEK(date_add(csa_sendtime,interval 6 day),2)  = " + week + "");
                }
                List<Map<String, Object>> cbsDelinerEntityList = systemService.findForJdbc(stringBuffer.toString());
                Integer sl1Number = 0;
                Integer countSum = 0;
                for (Map<String, Object> map : cbsDelinerEntityList) {
                    countSum++;
                    Integer integer = statisticalShipment(map.get("gettime").toString(), map.get("csa_sendtime").toString());
                    if (integer == 1) {
                        sl1Number++;
                    }
                }
                if (countSum != 0) {
                    String faultRateStr = df.format(Double.valueOf(sl1Number) / Double.valueOf(countSum));
                    faultRate = Double.valueOf(faultRateStr);
                }
                Map<String, Double> azMap = new HashMap();
                if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                    azMap.put("" + startTime + "-" + endTime + "", faultRate);
                } else if (StringUtil.isNotEmpty(week)) {
                    azMap.put("第" + week + "周", faultRate);
                } else {
                    azMap.put(changeDate(nowMon), faultRate);
                }
                azFaultRate.add(azMap);
            } else {
                Map<String, Double> azMap = new HashMap();
                if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                    azMap.put("" + startTime + "-" + endTime + "", faultRate);
                } else if (StringUtil.isNotEmpty(week)) {
                    azMap.put("第" + week + "周", faultRate);
                } else {
                    azMap.put(changeDate(nowMon), faultRate);
                }
                azFaultRate.add(azMap);
            }

        } else {
            for (String month : monthBetween) {
                Integer minNumber = 0;
                Integer maxNumber = 0;
                List<Map<String, Object>> cbsDeliverMapList = systemService.findForJdbc("select gettime,csa_sendtime,not_qualified,eta_qunqualified from cbs_deliver lesson where  date_format(csa_sendtime,'%Y-%m')='" + month + "' and deliver_logistics = '" + name + "' AND `status` = 7 ");
                //实际时效为SL1
                List<Map<String, Object>> SL1List = new ArrayList<>();
                List<Map<String, Object>> SL2List = new ArrayList<>();
                for (Map<String, Object> map : cbsDeliverMapList) {
                    maxNumber++;
                    Integer integer = statisticalShipment(map.get("gettime").toString(), map.get("csa_sendtime").toString());
                    if (integer == 1) {
                        SL1List.add(map);
                        SL2List.add(map);
                        minNumber++;
                    }
                    if (integer == 2) {
                        SL2List.add(map);

                    }

                }
                double faultRate = 0;
                if (maxNumber != 0) {
                    String faultRateStr = null;
                    if ("SL2".equals(slaType)) {
                        faultRateStr = df.format(Double.valueOf(SL2List.size()) / Double.valueOf(cbsDeliverMapList.size()));
                    } else {
                        faultRateStr = df.format(Double.valueOf(SL1List.size()) / Double.valueOf(cbsDeliverMapList.size()));

                    }
                    faultRate = Double.valueOf(faultRateStr);
                }
                if ("Target".equals(name)) {
                    Map<String, Double> azMap = new HashMap();
                    if ("SL1".equals(slaType)) {
                        azMap.put(changeDate(month), 0.75);
                    } else {
                        azMap.put(changeDate(month), 0.9);
                    }
                    azFaultRate.add(azMap);
                } else if ("Combine".equals(name)) {
                    List<Map<String, Object>> cbsDelinerEntityList = systemService.findForJdbc("select gettime,csa_sendtime from cbs_deliver lesson where  date_format(csa_sendtime,'%Y-%m')='" + month + "' and status = 7");
                    Integer sl1Number = 0;
                    Integer countSum = 0;
                    for (Map<String, Object> map : cbsDelinerEntityList) {
                        countSum++;
                        Integer integer = statisticalShipment(map.get("gettime").toString(), map.get("csa_sendtime").toString());
                        if (integer == 1) {
                            sl1Number++;
                            SL1List.add(map);
                            SL2List.add(map);
                        }
                        if (integer == 2) {
                            SL2List.add(map);
                        }
                    }
                    if (countSum != 0) {
                        if ("SL2".equals(slaType)) {
                            String faultRateStr = df.format(Double.valueOf(SL2List.size()) / Double.valueOf(countSum));
                            faultRate = Double.valueOf(faultRateStr);
                        } else {
                            String faultRateStr = df.format(Double.valueOf(sl1Number) / Double.valueOf(countSum));
                            faultRate = Double.valueOf(faultRateStr);
                        }
                    }
                    Map<String, Double> azMap = new HashMap();
                    azMap.put(changeDate(month), faultRate);
                    azFaultRate.add(azMap);
                } else {
                    Map<String, Double> azMap = new HashMap();
                    azMap.put(changeDate(month), faultRate);
                    azFaultRate.add(azMap);
                }
            }
        }
        Highchart hcAzFaultRate = new Highchart();
        hcAzFaultRate.setName(name);
        hcAzFaultRate.setData(azFaultRate);
        rsList.add(hcAzFaultRate);

        return rsList;
    }

    /*Net,修改，加上非物流达标数*/
    @Override
    public List<Highchart> getSl2Percentage(String deliverLogticis, String slaType, String yue, String week, String startTime, String endTime, boolean b) throws Exception {

        List<Highchart> highcharts = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("YYYY");
        String nowYear = sdf.format(new Date());
        String JanMon = nowYear + "-01";
        String DecMon = nowYear + "-12";
        List<String> monthBetween = getMonthBetween(JanMon, DecMon);
        DecimalFormat df = new DecimalFormat("0.0000");
        List<Map<String, Double>> azFaultRate = new ArrayList<>();
        Integer num = 0;
        if (b) {
            String nowMon = null;
            /**
             * update by x on 20181108 修复 bug 8879 精简代码 更换数量查询sql
             */
            //条件语句
            StringBuffer conditionSql = new StringBuffer();
            conditionSql.append(" AND deliver_type in ('1','2','6') ");
            if (StringUtil.isNotEmpty(yue)) {
                nowMon = nowYear + "-" + yue;
                conditionSql.append(" and date_format(csa_sendtime,'%Y-%m')='" + nowMon + "'");
            }
            if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                conditionSql.append(" AND csa_sendtime BETWEEN '" + startTime + "' and '" + endTime + "'");
            }
            if (StringUtil.isNotEmpty(week)) {
                conditionSql.append(" and WEEK(date_add(csa_sendtime,interval 6 day),2)  = " + week + "");
            }

            Long finishCount =
                    systemService.getCountForJdbc(String.format("SELECT COUNT(*) from cbs_deliver where `status` = 7 AND deliver_logistics = '" + deliverLogticis + "' %s ", conditionSql.toString()));
            if ("SL1".equals(slaType)) {
                Long sl1Count =
                        systemService.getCountForJdbc(String.format("select COUNT(*) from cbs_deliver where TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <=24 and `status` = 7 and deliver_logistics = '" + deliverLogticis + "' %s ", conditionSql.toString()));
                Long notSl1Count =
                        systemService.getCountForJdbc(String.format("select COUNT(*) from cbs_deliver where ((not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))) and  TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) >24 and wl_SLA = 'SL1' and `status` = 7 and deliver_logistics = '" + deliverLogticis + "' %s ", conditionSql.toString()));
                num = sl1Count.intValue() + notSl1Count.intValue();
            }else if("SL2".equals(slaType)){
                Long sl2Count =
                        systemService.getCountForJdbc(String.format("select COUNT(*) from cbs_deliver where TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <=48 and `status` = 7 and deliver_logistics = '" + deliverLogticis + "' %s ", conditionSql.toString()));
                Long notSl2Count =
                        systemService.getCountForJdbc(String.format("select COUNT(*) from cbs_deliver where ("
                        		+ "(not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))"
                        		+ ") and  TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) >48 and wl_SLA <= 'SL2' and `status` = 7 and deliver_logistics = '" + deliverLogticis + "' %s ", conditionSql.toString()));
                num = sl2Count.intValue() + notSl2Count.intValue();
            }

            double faultRate = 0;
            if (finishCount != 0) {
                String faultRateStr = df.format(Double.valueOf(num) / Double.valueOf(finishCount));
                faultRate = Double.valueOf(faultRateStr);
            }

            if ("Target".equals(deliverLogticis)) {
                Map<String, Double> azMap = new HashMap();
                if ("SL1".equals(slaType)) {
                    if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                        azMap.put("" + startTime + "-" + endTime + "", 0.75);
                    } else if (StringUtil.isNotEmpty(week)) {
                        azMap.put("第" + week + "周", 0.75);
                    } else {
                        azMap.put(changeDate(nowMon), 0.75);
                    }
                } else {
                    if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                        azMap.put("" + startTime + "-" + endTime + "", 0.9);
                    } else if (StringUtil.isNotEmpty(week)) {
                        azMap.put("第" + week + "周", 0.9);
                    } else {
                        azMap.put(changeDate(nowMon), 0.9);
                    }
                }
                azFaultRate.add(azMap);
            } else if ("Combine".equals(deliverLogticis)) {
                Long combineFinishCount = systemService.getCountForJdbc(String.format("SELECT COUNT(*) from cbs_deliver where `status` = 7 %s ", conditionSql));
                if ("SL1".equals(slaType)) {
                    Long sl1Count = systemService.getCountForJdbc(String.format("select COUNT(*) from cbs_deliver where TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <=24 and `status` = 7 %s ", conditionSql));
                    Long notSl1Count = systemService.getCountForJdbc(String.format("select COUNT(*) from cbs_deliver where ((not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))) and  TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) >24 and wl_SLA = 'SL1' and `status` = 7 %s ", conditionSql));
                    num = sl1Count.intValue() + notSl1Count.intValue();
                }else if("SL2".equals(slaType)){
                    Long sl2Count = systemService.getCountForJdbc(String.format("select COUNT(*) from cbs_deliver where TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <=48 and `status` = 7 %s ", conditionSql));
                    Long notSl2Count = systemService.getCountForJdbc(String.format("select COUNT(*) from cbs_deliver where (n(not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))) and  TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) >48 and wl_SLA <= 'SL2' and `status` = 7 %s ", conditionSql));
                    num = sl2Count.intValue() + notSl2Count.intValue();
                }

                if (combineFinishCount != 0) {
                    String faultRateStr = df.format(Double.valueOf(num) / Double.valueOf(combineFinishCount));
                    faultRate = Double.valueOf(faultRateStr);
                }
                /**
                 * update by x on 20181108 修复 bug 8879 精简代码 更换数量查询sql   END ---
                 */
                Map<String, Double> azMap = new HashMap();
                if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                    azMap.put("" + startTime + "-" + endTime + "", faultRate);
                } else if (StringUtil.isNotEmpty(week)) {
                    azMap.put("第" + week + "周", faultRate);
                } else {
                    azMap.put(changeDate(nowMon), faultRate);
                }
                azFaultRate.add(azMap);
            } else {
                Map<String, Double> azMap = new HashMap();
                if (StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)) {
                    azMap.put("" + startTime + "-" + endTime + "", faultRate);
                } else if (StringUtil.isNotEmpty(week)) {
                    azMap.put("第" + week + "周", faultRate);
                } else {
                    azMap.put(changeDate(nowMon), faultRate);
                }
                azFaultRate.add(azMap);
            }
        } else {

            for (String month : monthBetween) {
                /**
                 * update by X on 20181106 修复 bug 8879
                 * Net （实际达成SL1(SL2)的发货单数+没有达成SL1(SL2)的发货单中两个未达标原因有其一是“非物流原因”的发货单数）/当月本物流已签收的发货单总数。
                 */
                Long finishCount =
                        systemService.getCountForJdbc("SELECT COUNT(*) from cbs_deliver where `status` = 7 AND deliver_type in ('1','2','6') and DATE_FORMAT(csa_sendtime,'%Y-%m') = '" + month + "' AND deliver_logistics = '" + deliverLogticis + "'");
                if ("SL1".equals(slaType)) {
                    Long sl1Count = systemService.getCountForJdbc("select COUNT(*) from cbs_deliver where TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <=24 and `status` = 7 AND deliver_type in ('1','2','6') and deliver_logistics = '" + deliverLogticis + "' and date_format(csa_sendtime,'%Y-%m')='" + month + "'");
                    Long notSl1Count = systemService.getCountForJdbc("select COUNT(*) from cbs_deliver where ((not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))) and  TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) >24 and wl_SLA = 'SL1' and `status` = 7 AND deliver_type in ('1','2','6') and deliver_logistics = '" + deliverLogticis + "' and date_format(csa_sendtime,'%Y-%m')='" + month + "'");
                    num = sl1Count.intValue() + notSl1Count.intValue();
                }else if("SL2".equals(slaType)){
                    Long sl2Count = systemService.getCountForJdbc("select COUNT(*) from cbs_deliver where TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <=48 and `status` = 7 AND deliver_type in ('1','2','6') and deliver_logistics = '" + deliverLogticis + "' and date_format(csa_sendtime,'%Y-%m')='" + month + "'");
                    Long notSl2Count = systemService.getCountForJdbc("select COUNT(*) from cbs_deliver where ((not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))) and  TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) >48 and wl_SLA <= 'SL2' and `status` = 7 AND deliver_type in ('1','2','6') and deliver_logistics = '" + deliverLogticis + "' and date_format(csa_sendtime,'%Y-%m')='" + month + "'");
                    num = sl2Count.intValue() + notSl2Count.intValue();
                }

                double faultRate = 0;
                if (finishCount != 0) {
                    String faultRateStr = df.format(Double.valueOf(num) / Double.valueOf(finishCount));
                    faultRate = Double.valueOf(faultRateStr);
                }
                /**
                 * update by X on 20181106 修复 bug 8879
                 * Net （实际达成SL1(SL2)的发货单数+没有达成SL1(SL2)的发货单中两个未达标原因有其一是“非物流原因”的发货单数）/当月本物流已签收的发货单总数。 END ---
                 */

                if ("Target".equals(deliverLogticis)) {
                    Map<String, Double> azMap = new HashMap();
                    if ("SL1".equals(slaType)) {
                        azMap.put(changeDate(month), 0.75);
                    } else {
                        azMap.put(changeDate(month), 0.9);
                    }
                    azFaultRate.add(azMap);
                } else if ("Combine".equals(deliverLogticis)) {
                    /**
                     * update by x on 20181107 修复 bug 8879 Combine的算法不同于其余三种
                     */
                    //Combine 分母
                    Long combineFinishCount = systemService.getCountForJdbc("SELECT COUNT(*) from cbs_deliver where `status` = 7 AND deliver_type in ('1','2','6') and date_format(csa_sendtime,'%Y-%m')='" + month + "' ");
                    if ("SL1".equals(slaType)) {
                        Long sl1Count = systemService.getCountForJdbc("select COUNT(*) from cbs_deliver where TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <=24 and `status` = 7  AND deliver_type in ('1','2','6') and date_format(csa_sendtime,'%Y-%m')='" + month + "'");
                        Long notSl1Count = systemService.getCountForJdbc("select COUNT(*) from cbs_deliver where ((not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))) and  TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) >24 and wl_SLA = 'SL1' and `status` = 7 AND deliver_type in ('1','2','6') and date_format(csa_sendtime,'%Y-%m')='" + month + "'");
                        num = sl1Count.intValue() + notSl1Count.intValue();
                    }else if("SL2".equals(slaType)){
                        Long sl2Count = systemService.getCountForJdbc("select COUNT(*) from cbs_deliver where TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <=48 and `status` = 7 and date_format(csa_sendtime,'%Y-%m')='" + month + "'");
                        Long notSl2Count = systemService.getCountForJdbc("select COUNT(*) from cbs_deliver where ((not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))) and  TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) >48 and wl_SLA <= 'SL2' and `status` = 7 AND deliver_type in ('1','2','6') and date_format(csa_sendtime,'%Y-%m')='" + month + "'");
                        num = sl2Count.intValue() + notSl2Count.intValue();
                    }

                    if (combineFinishCount != 0) {
                        String faultRateStr = df.format(Double.valueOf(num) / Double.valueOf(combineFinishCount));
                        faultRate = Double.valueOf(faultRateStr);
                    }
                    /**
                     * update by x on 20181107 修复 bug 8879 Combine的算法不同于其余三种 END ---
                     */


                    Map<String, Double> azMap = new HashMap();
                    azMap.put(changeDate(month), faultRate);
                    azFaultRate.add(azMap);
                } else {
                    Map<String, Double> azMap = new HashMap();
                    azMap.put(changeDate(month), faultRate);
                    azFaultRate.add(azMap);
                }
            }
        }
        Highchart hcAzFaultRate = new Highchart();
        hcAzFaultRate.setName(deliverLogticis);
        hcAzFaultRate.setData(azFaultRate);
        highcharts.add(hcAzFaultRate);

        return highcharts;
    }

    //
    public String changeDate(String dateStr) throws Exception {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM");
        SimpleDateFormat sdf = new SimpleDateFormat("MMM-yy", Locale.ENGLISH);
        Date date = df.parse(dateStr);
        String result = sdf.format(date);

        return result;
    }

    /*
     * */
    public List<String> getMonthBetween(String minDate, String maxDate) throws Exception {
        ArrayList<String> result = new ArrayList<String>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");//格式化为年月

        Calendar min = Calendar.getInstance();
        Calendar max = Calendar.getInstance();

        min.setTime(sdf.parse(minDate));
        min.set(min.get(Calendar.YEAR), min.get(Calendar.MONTH), 1);

        max.setTime(sdf.parse(maxDate));
        max.set(max.get(Calendar.YEAR), max.get(Calendar.MONTH), 2);

        Calendar curr = min;
        while (curr.before(max)) {
            result.add(sdf.format(curr.getTime()));
            curr.add(Calendar.MONTH, 1);
        }

        return result;
    }

    //小数点转换为百分比
    public String numberToPer(int num, int num2) {
        DecimalFormat df = new DecimalFormat("0.00");
        return df.format(num / num2);
    }

    //计算实际SLA等级（用于统计报表）
    public Integer statisticalShipment(String maxTime, String minTime) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        Date newDate = sdf.parse(maxTime);
        Date oldDate = sdf.parse(minTime);
        long time = newDate.getTime();
        long oldtime = oldDate.getTime();
        long diff = 1;
        diff = time - oldtime;
        if (diff <= 0) {
            diff = 1;
        }
        double hours = diff / 1000 / 60 / 60.0;
        DecimalFormat df = new DecimalFormat("##.#");
        String dff = df.format(hours);

        if (Double.valueOf(dff) <= 24) {
            return 1;
        }
        if (Double.valueOf(dff) > 24 && Double.valueOf(dff) <= 48) {
            return 2;
        }
        if (Double.valueOf(dff) > 48 && Double.valueOf(dff) <= 72) {
            return 3;
        }
        if (Double.valueOf(dff) > 72) {
            return 4;
        }
        return 0;
    }

    //报表导出
    public void exportCbsDeliverReport(HttpServletRequest request, HttpServletResponse response) {
        TSUser user = ResourceUtil.getSessionUserName();
        String startTime = request.getParameter("startTime");
        String endTime = request.getParameter("endTime");
        OutputStream out = null;
        try {
            String fileName = "物流发货报表.xlsx";
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-msdownload;");
            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
            out = response.getOutputStream();
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT\n" +
                    "\tDISTINCT cd.id as id,\n" +
                    "\tWEEK (\n" +
                    "\t\tdate_add(\n" +
                    "\t\t\tcd.create_date,\n" +
                    "\t\t\tINTERVAL 6 DAY\n" +
                    "\t\t),\n" +
                    "\t\t2\n" +
                    "\t) AS weekConut,\n" +
                    "\n" +
                    "\tIFNULL(cd.csa_sendtime, '') AS csaSendTime,\n" +
                    "\tCASE\n" +
                    "WHEN cde.equipment_apply_detail_id IS NULL THEN\n" +
                    "\t'CSA'\n" +
                    "ELSE\n" +
                    "\t(\n" +
                    "\t\tSELECT\n" +
                    "\t\t\tdp.shortname\n" +
                    "\t\tFROM\n" +
                    "\t\t\tt_s_base_user bu\n" +
                    "\t\tLEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID\n" +
                    "\t\tLEFT JOIN t_s_depart dp ON dp.ID = uo.org_id\n" +
                    "\t\tWHERE\n" +
                    "\t\t\tbu.id IN (\n" +
                    "\t\t\t\tSELECT\n" +
                    "\t\t\t\t\tu.id\n" +
                    "\t\t\t\tFROM\n" +
                    "\t\t\t\t\tt_s_role r\n" +
                    "\t\t\t\tLEFT JOIN t_s_role_user ru ON r.id = ru.roleid\n" +
                    "\t\t\t\tLEFT JOIN t_s_user u ON ru.userid = u.id\n" +
                    "\t\t\t\tWHERE\n" +
                    "\t\t\t\t\tu.id IN (\n" +
                    "\t\t\t\t\t\tSELECT\n" +
                    "\t\t\t\t\t\t\tid\n" +
                    "\t\t\t\t\t\tFROM\n" +
                    "\t\t\t\t\t\t\tt_s_base_user\n" +
                    "\t\t\t\t\t\tWHERE\n" +
                    "\t\t\t\t\t\t\tusername = detail.create_by\n" +
                    "\t\t\t\t\t)\n" +
                    "\t\t\t)\n" +
                    "\t)\n" +
                    "END AS 'applyThird',\n" +
                    " IFNULL(cd.so_code, '') AS so,\n" +
                    " IFNULL(cd.space_number, '') AS spaceNumber,\n" +
                    " ifnull(cde.total, '0') AS total,\n" +
                    " IFNULL(cde.weight, '0') AS weight,\n" +
                    " IFNULL(cd.deliver_logistics, '') AS logistics,\n" +
                    " IFNULL(wa.`name`, '') AS `name`,\n" +
                    " CASE\n" +
                    "WHEN cd.warehouse_position = '1' THEN\n" +
                    "\t'库位1'\n" +
                    "WHEN cd.FH_SLA = '2' THEN\n" +
                    "\t'库位2' \n" +
                    "\t\tELSE\n" +
                    "\t\t\t''\n" +
                    "\t\tEND AS kw,\n" +
                    "\tIFNULL(wa.`name`, '') AS kwName,\n" +
                    " IFNULL(te.territoryname, '') AS territoryname,\n" +
                    " IFNULL(t.territoryname, '') AS shengName,\n" +
                    " CONCAT(cd.user_name,cd.phone,'') as userNameAndPhone,\n\n" +
                    " CASE\n" +
                    "WHEN cd.FH_SLA = '1' THEN\n" +
                    "\t' 普通'\n" +
                    "WHEN cd.FH_SLA = '2' THEN\n" +
                    "\t'加急' WHEN\n" +
                    "\t\tcd.FH_SLA = '3' THEN\n" +
                    "\t\t\t'当天'\n" +
                    "\t\tELSE\n" +
                    "\t\t\t''\n" +
                    "\t\tEND AS fhSla,\n" +
                    "\t\tIFNULL(cd.logistics_sendtime, '') AS logisticsSendTime,\n" +
                    "\t\tIFNULL(cd.wl_SLA, '') AS wlSla,\n" +
                    "\t\tIFNULL(cde.logistics_number, '') AS logistics_number,\n" +
                    "\t\tIFNULL(cd.expecttime, '') AS eta,\n" +
                    "\t\tIFNULL(log.create_date, '') wlSignTime,\n" +
                    "\t\tIFNULL(cd.gettime, '') signTime,\n" +
                    "\t\tFORMAT(\n" +
                    "\t\t\tTIMESTAMPDIFF(\n" +
                    "\t\t\t\tSECOND,\n" +
                    "\t\t\t\tcd.csa_sendtime,\n" +
                    "\t\t\t\tcd.gettime\n" +
                    "\t\t\t) / 60 / 60,\n" +
                    "\t\t\t2\n" +
                    "\t\t) AS logisticsDuration,\n" +
                    "\t\tCASE\n" +
                    "\tWHEN FORMAT(TIMESTAMPDIFF(\n" +
                    "\t\tSECOND,\n" +
                    "\t\tcd.csa_sendtime,\n" +
                    "\t\tgettime\n" +
                    "\t)/ 60 / 60,2) <= 24 THEN\n" +
                    "\t\t'SL1'\n" +
                    "\tWHEN (\n" +
                    "\t\tFORMAT(TIMESTAMPDIFF(\n" +
                    "\t\t\tSECOND,\n" +
                    "\t\t\tcd.csa_sendtime,\n" +
                    "\t\t\tgettime\n" +
                    "\t\t) / 60 / 60,2) > 24\n" +
                    "\t\tAND FORMAT(TIMESTAMPDIFF(\n" +
                    "\t\t\tSECOND,\n" +
                    "\t\t\tcd.csa_sendtime,\n" +
                    "\t\t\tgettime\n" +
                    "\t\t) / 60 / 60,2) <= 48\n" +
                    "\t) THEN\n" +
                    "\t\t'SL2'\n" +
                    "\tWHEN (\n" +
                    "\t\tFORMAT(TIMESTAMPDIFF(\n" +
                    "\t\t\tSECOND,\n" +
                    "\t\t\tcd.csa_sendtime,\n" +
                    "\t\t\tgettime\n" +
                    "\t\t)/ 60 / 60,2) > 48\n" +
                    "\t\tAND FORMAT(TIMESTAMPDIFF(\n" +
                    "\t\t\tSECOND,\n" +
                    "\t\t\tcd.csa_sendtime,\n" +
                    "\t\t\tgettime\n" +
                    "\t\t)/ 60 / 60,2) <= 72\n" +
                    "\t) THEN\n" +
                    "\t\t'SL3'\n" +
                    "\tWHEN (\n" +
                    "\t\tFORMAT(TIMESTAMPDIFF(\n" +
                    "\t\t\tSECOND,\n" +
                    "\t\t\tcd.csa_sendtime,\n" +
                    "\t\t\tgettime\n" +
                    "\t\t)/ 60 / 60,2) > 72\n" +
                    "\t) THEN\n" +
                    "\t\t'SL4'\n" +
                    "\tELSE\n" +
                    "\t\t''\n" +
                    "\tEND AS slaType,\n" +
                    "\tCASE\n" +
                    "WHEN cd.is_standard = '1' THEN\n" +
                    "\t'Y'\n" +
                    "WHEN cd.is_standard = '2' THEN\n" +
                    "\t'N'\n" +
                    "ELSE\n" +
                    "\t''\n" +
                    "END AS performance,\n" +
                    "IFNULL(cd.not_qualified, '') AS notQualifiled,\n" +
                    " \n" +
                    "IFNULL(cd.not_qualified_memo, '') AS notQualifiledmemo,\n" +
                    " \n" +
                    "IFNULL(cd.eta_qunqualified, '') AS etanotQualifiled,\n" +
                    " \n" +
                    "IFNULL(cd.eta_not_qualified_memo, '') AS etanotQualifiledmemo,\n" +
                    " \n" +
                    " CASE\n" +
                    "WHEN cde.isTrack = '1' THEN\n" +
                    "\t'Y'\n" +
                    "WHEN cde.isTrack = '2' THEN\n" +
                    "\t'N'\n" +
                    "ELSE\n" +
                    "\t''\n" +
                    "END AS isSn,\n" +
                    " IFNULL(cde.deliverSn, '') AS deliverSn,\n" +
                    " CASE\n" +
                    "WHEN cd.sign_file IS NULL THEN\n" +
                    "\t'N'\n" +
                    "ELSE\n" +
                    "\t'Y'\n" +
                    "END AS signFile\n" +
                    "FROM\n" +
                    "\tcbs_deliver cd\n" +
                    "LEFT JOIN (\n" +
                    "\tSELECT\n" +
                    "\t\tCOUNT(type_des) AS total,\n" +
                    "\t\ttype_des AS type,\n" +
                    "\t\tdeliver_id AS deliver_id,\n" +
                    "\t\tweight AS weight,\n" +
                    "\t\tlogistics_number AS logistics_number,\n" +
                    "\t\tequipment_apply_detail_id AS equipment_apply_detail_id,\n" +
                    "\t\tapply_layer AS apply_layer,\n" +
                    "\t\tdeliver_sn AS deliverSn,\n" +
                    "\t\ttype_id AS typeId,\n" +
                    "\t\titem.is_track as isTrack\n" +
                    "\tFROM\n" +
                    "\t\t`cbs_deliver_equipment` \n" +
                    "\tLEFT JOIN zy_es_item item on item.ID = `cbs_deliver_equipment`.item_id\n" +
                    "\tGROUP BY\n" +
                    "\t\tdeliver_id,\n" +
                    "\t\ttype_id\n" +
                    ") cde ON cd.id = cde.deliver_id\n" +
                    "LEFT JOIN cbs_equipment_apply_detail detail ON detail.id = cde.equipment_apply_detail_id\n" +
                    "LEFT JOIN cbs_warehouse wa ON cd.deliver_warehouse_id = wa.id\n" +
                    "LEFT JOIN t_s_territory te ON te.id = cd.shi\n" +
                    "LEFT JOIN t_s_territory t ON t.id = cd.sheng\n"+
                    "LEFT JOIN (SELECT MAX(create_date) as create_date,business_id from cbs_handle_log GROUP BY business_id ) log ON log.business_id=cd.id \n");
            sql.append(" WHERE cd.status = 7 and cd.deliver_type in ('1','2','6') ");
            if (StringUtil.isNotEmpty(startTime)) {
            	 sql.append(" and DATE_FORMAT(cd.csa_sendtime,'%Y-%m-%d') >='"+startTime+"'");
            }
            if (StringUtil.isNotEmpty(endTime)) {
           	 sql.append(" and DATE_FORMAT(cd.csa_sendtime,'%Y-%m-%d') <='"+endTime+"'");
           }
            List<Map<String, Object>> reusltMaps = super.findForJdbc(sql.toString());

            systemService.addLog(user.getRealName() + "导出" + reusltMaps.size() + "条物流发货记录", Globals.Log_Type_OTHER, Globals.Log_Leavel_INFO);

            InputStream is = ZyInspectionPlanServiceImpl.class.getClassLoader().getResourceAsStream("jeecg/template/cbsDeliverReportList.xlsx");
            XSSFWorkbook xssf = new XSSFWorkbook(is);
            XSSFSheet sheet = xssf.getSheetAt(0);
            int rowNum = 1;
            for (Map<String, Object> dataMap : reusltMaps) {
                XSSFRow row = sheet.createRow(rowNum);
                int cellNum = 0, index = 0;
                for (String key : dataMap.keySet()) {
                    if (index == 0) {
                        index++;
                        continue;
                    }
                    XSSFCell cell = row.createCell(cellNum);
                    cell.setCellValue(dataMap.get(key) != null ? dataMap.get(key).toString() : "");
                    cellNum++;
                }
                rowNum++;
            }
            xssf.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public String getDelinerCode() {
        Date date = new Date();
        DateFormat format = new SimpleDateFormat("YYYYMMdd");
        String name = "FH" + format.format(date);
        Map<String,Object> m = super.findOneForJdbc("select nextSeqVal(?) seqval", name);
        String fhNum = name + String.format("%3d",Integer.valueOf(m.get("seqval").toString())).replace(" ","0");
        return fhNum;
    }

    public CbsReturnEquipmentEntity getcbsReturnEquipmentEntiy() {
        CbsReturnEquipmentEntity cbsReturnEquipmentEntity = new CbsReturnEquipmentEntity();
        return cbsReturnEquipmentEntity;

    }

    public CbsReturnEquipmentDetailEntity getCbsReturnEquipmentDetailEntity() {
        CbsReturnEquipmentDetailEntity cbsReturnEquipmentDetailEntity = new CbsReturnEquipmentDetailEntity();
        return cbsReturnEquipmentDetailEntity;
    }

    @Override
    public AjaxJson sendHuo(CbsDelinerEntity cbsDelinerEntity, HttpServletRequest request) {
        AjaxJson ajaxJson = new AjaxJson();
        String msg = "发货成功";
        boolean flag = true;
        //获取登录的用户信息
        TSUser user = ResourceUtil.getSessionUserName();
        Date date = new Date();
        DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = format.format(date);
        String equipmentIds = request.getParameter("EquipmentIds");//备件
        //原物流
        String exLogisticsName = "";
        List<String> exCbsDelinerEntity = systemService.findListbySql("select deliver_logistics from cbs_deliver where id = '" + cbsDelinerEntity.getId() + "'");
        if (exCbsDelinerEntity != null && exCbsDelinerEntity.size() > 0) {
            exLogisticsName = exCbsDelinerEntity.get(0);
        }
        try {

            cbsDelinerEntity.setFhCSA(user);
            systemService.saveOrUpdate(cbsDelinerEntity);
            String deliverLogistics = cbsDelinerEntity.getDeliverLogistics();
            if (StaticValue.wldfh.equals(cbsDelinerEntity.getStatus()) || StaticValue.wlyfh.equals(cbsDelinerEntity.getStatus())) {
                systemService.updateBySqlString("update cbs_deliver set status = " + StaticValue.wldfh + ",csa_sendtime='" + time + "',deliver_logistics = '" + deliverLogistics + "',deliver_des = '" + cbsDelinerEntity.getDeliverDes() + "',FH_SLA = '" + cbsDelinerEntity.getFhSLA() + "'" +
                        ",deliver_warehouse_id='" + cbsDelinerEntity.getDeliverWarehouseId().getId() + "',return_logistics ='" + cbsDelinerEntity.getReturnLogistics() + "' where id = '" + cbsDelinerEntity.getId() + "'");
                if (cbsDelinerEntity.getThSheng().getId() != null && cbsDelinerEntity.getThSheng().getId() != "" && cbsDelinerEntity.getThCity().getId() != null && cbsDelinerEntity.getThCity().getId() != "") {
                    systemService.updateBySqlString("update cbs_deliver set th_sheng = '" + cbsDelinerEntity.getThSheng().getId() + "',th_city='" + cbsDelinerEntity.getThCity().getId() + "',th_address='" + cbsDelinerEntity.getThAddress() + "' where id = '" + cbsDelinerEntity.getId() + "'");
                }
            }
            if ("1".equals(cbsDelinerEntity.getStatus())) {  /*CSA待发货状态下，才进行发货*/
                systemService.updateBySqlString("update cbs_deliver set status = " + StaticValue.wldfh + ",csa_sendtime='" + time + "',deliver_logistics ='" + deliverLogistics + "',deliver_des ='" + cbsDelinerEntity.getDeliverDes() + "',FH_SLA = '" + cbsDelinerEntity.getFhSLA() + "'" +
                        ",deliver_warehouse_id='" + cbsDelinerEntity.getDeliverWarehouseId().getId() + "',return_logistics ='" + cbsDelinerEntity.getReturnLogistics() + "' where id = '" + cbsDelinerEntity.getId() + "'");
                if (cbsDelinerEntity.getThSheng().getId() != null && cbsDelinerEntity.getThSheng().getId() != "" && cbsDelinerEntity.getThCity().getId() != null && cbsDelinerEntity.getThCity().getId() != "") {
                    systemService.updateBySqlString("update cbs_deliver set th_sheng = '" + cbsDelinerEntity.getThSheng().getId() + "',th_city='" + cbsDelinerEntity.getThCity().getId() + "',th_address='" + cbsDelinerEntity.getThAddress() + "' where id = '" + cbsDelinerEntity.getId() + "'");
                }
                String string = "";
                String string1 = "";
                if (equipmentIds.contains(",")) {
                    int j = 0;
                    for (String id : equipmentIds.split(",")) {
                        CbsDeliverEquipmentEntity cbsDeliverEquipmentEntity = systemService.getEntity(CbsDeliverEquipmentEntity.class, id.trim());
                        if (cbsDeliverEquipmentEntity != null) {

                            if (cbsDeliverEquipmentEntity.getIsReturn() != null && cbsDeliverEquipmentEntity.getIsReturn() == 2) {
                                CbsReturnEquipmentEntity cbsReturnEquipmentEntity = getcbsReturnEquipmentEntiy();
                                CbsReturnEquipmentDetailEntity cbsReturnEquipmentDetailEntity1 = getCbsReturnEquipmentDetailEntity();
                                //TODO 创建返件任务
                                Date date1 = new Date();
                                DateFormat format1 = new SimpleDateFormat("yyyyMMdd");
                                String time1 = format1.format(date1);
                                StringBuffer buffer = new StringBuffer();
                                buffer.append("FJ");
                                buffer.append(time1);
                                List<Object[]> objects = systemService.findListbySql("select * from cbs_return_equipment where to_days(create_date) = to_days(now()) ORDER BY `code` desc;");
                                if (objects.size() == 0) {
                                    buffer.append("001");
                                    string1 = buffer.toString();
                                    CbsReturnEquipmentEntity returnEquipmentEntity = null;
                                    if (j == 0) {
                                        //判断返件表中是否存在该数据
                                        returnEquipmentEntity = systemService.singleResult("from CbsReturnEquipmentEntity where deliverCode = '" + cbsDelinerEntity.getCode() + "'");
                                        if (returnEquipmentEntity == null) {
                                            cbsReturnEquipmentEntity.setCode(string1);
                                            cbsReturnEquipmentEntity.setSrCode(cbsDelinerEntity.getSrCode());
                                            cbsReturnEquipmentEntity.setCinemaName(cbsDelinerEntity.getCinemaName());
                                            cbsReturnEquipmentEntity.setName(cbsDelinerEntity.getUserName());
                                            cbsReturnEquipmentEntity.setPhone(cbsDelinerEntity.getPhone());
                                            cbsReturnEquipmentEntity.setAddress(cbsDelinerEntity.getAddress());
                                            cbsReturnEquipmentEntity.setSoCode(cbsDelinerEntity.getSoCode());
                                            cbsReturnEquipmentEntity.setDeliverCode(cbsDelinerEntity.getCode());
                                            cbsReturnEquipmentEntity.setCrCode(cbsDelinerEntity.getCrCode());
                                            cbsReturnEquipmentEntity.setEquipmentApplyId(cbsDelinerEntity.getEquipmentApplyId());
                                            cbsReturnEquipmentEntity.setStatus(StaticValue.RETURN_STATUS_Log_Type_1);
                                            cbsReturnEquipmentEntity.setLogistics(cbsDelinerEntity.getReturnLogistics());
                                            cbsReturnEquipmentEntity.setLogisticsCode(cbsDeliverEquipmentEntity.getLogisticsNumber());
                                            cbsReturnEquipmentEntity.setSheng(cbsDelinerEntity.getSheng());
                                            cbsReturnEquipmentEntity.setShi(cbsDelinerEntity.getShi());
                                            systemService.save(cbsReturnEquipmentEntity);
                                            j++;
                                        }
                                    }
                                    if (returnEquipmentEntity == null) {
                                        List<Map<String, Object>> forJdbc = systemService.findForJdbc("SELECT\n" +
                                                "*\n" +
                                                "FROM\n" +
                                                "\tcbs_return_equipment_detail\n" +
                                                "WHERE\n" +
                                                "\tdeliver_equuipment_id = '" + equipmentIds.trim() + "'\n" +
                                                "AND return_equipment_id = '" + cbsReturnEquipmentEntity.getId() + "'");
                                        if (forJdbc == null || forJdbc.size() == 0) {
                                            List<CbsReturnEquipmentEntity> cbsReturnEquipmentEntity1 = systemService.findHql("from CbsReturnEquipmentEntity where deliverCode = '" + cbsDelinerEntity.getCode() + "'");
                                            if (cbsReturnEquipmentEntity1.size() > 0) {
                                                cbsReturnEquipmentDetailEntity1.setReturnEquipmentId(cbsReturnEquipmentEntity1.get(0));
                                            }
                                            cbsReturnEquipmentDetailEntity1.setTypeId(cbsDeliverEquipmentEntity.getTypeId());
                                            cbsReturnEquipmentDetailEntity1.setTypeDes(cbsDeliverEquipmentEntity.getTypeDes());
                                            cbsReturnEquipmentDetailEntity1.setItemId(cbsDeliverEquipmentEntity.getItemId());
                                            cbsReturnEquipmentDetailEntity1.setItem(cbsDeliverEquipmentEntity.getItem());
                                            cbsReturnEquipmentDetailEntity1.setItemTotal(cbsDeliverEquipmentEntity.getTotal());
                                            cbsReturnEquipmentDetailEntity1.setSn(cbsDeliverEquipmentEntity.getSn());
                                            cbsReturnEquipmentDetailEntity1.setDeliverEquipmentId(cbsDeliverEquipmentEntity);
                                            systemService.save(cbsReturnEquipmentDetailEntity1);
                                        }
                                    }


                                } else {
                                    String o = (String) objects.get(0)[1];
                                    String s = o.substring(10, 13);
                                    int i = Integer.parseInt(s);
                                    i++;
                                    if (i <= 9) {
                                        buffer.append("00" + i);
                                    } else if (i >= 10 && i < 100) {
                                        buffer.append("0" + i);
                                    } else {
                                        buffer.append(i);
                                    }
                                    string = buffer.toString();
                                    CbsReturnEquipmentEntity returnEquipmentEntity = null;
                                    if (j == 0) {
                                        //判断返件表中是否存在该数据
                                        returnEquipmentEntity = systemService.singleResult("from CbsReturnEquipmentEntity where deliverCode = '" + cbsDelinerEntity.getCode() + "'");
                                        if (returnEquipmentEntity == null) {
                                            cbsReturnEquipmentEntity.setCode(string);
                                            cbsReturnEquipmentEntity.setSrCode(cbsDelinerEntity.getSrCode());
                                            cbsReturnEquipmentEntity.setSoCode(cbsDelinerEntity.getSoCode());
                                            cbsReturnEquipmentEntity.setDeliverCode(cbsDelinerEntity.getCode());
                                            cbsReturnEquipmentEntity.setCrCode(cbsDelinerEntity.getCrCode());
                                            cbsReturnEquipmentEntity.setCinemaName(cbsDelinerEntity.getCinemaName());
                                            cbsReturnEquipmentEntity.setName(cbsDelinerEntity.getUserName());
                                            cbsReturnEquipmentEntity.setPhone(cbsDelinerEntity.getPhone());
                                            cbsReturnEquipmentEntity.setAddress(cbsDelinerEntity.getAddress());
                                            cbsReturnEquipmentEntity.setEquipmentApplyId(cbsDelinerEntity.getEquipmentApplyId());
                                            cbsReturnEquipmentEntity.setLogistics(cbsDelinerEntity.getReturnLogistics());
                                            cbsReturnEquipmentEntity.setLogisticsCode(cbsDeliverEquipmentEntity.getLogisticsNumber());
                                            cbsReturnEquipmentEntity.setStatus(StaticValue.RETURN_STATUS_Log_Type_1);
                                            cbsReturnEquipmentEntity.setSheng(cbsDelinerEntity.getSheng());
                                            cbsReturnEquipmentEntity.setShi(cbsDelinerEntity.getShi());
                                            systemService.save(cbsReturnEquipmentEntity);
                                            j++;
                                        }

                                    }
                                    if (returnEquipmentEntity == null) {
                                        List<Map<String, Object>> forJdbc = systemService.findForJdbc("SELECT\n" +
                                                "*\n" +
                                                "FROM\n" +
                                                "\tcbs_return_equipment_detail\n" +
                                                "WHERE\n" +
                                                "\tdeliver_equuipment_id = '" + equipmentIds.trim() + "'\n" +
                                                "AND return_equipment_id = '" + cbsReturnEquipmentEntity.getId() + "'");
                                        if (forJdbc == null || forJdbc.size() == 0) {
                                            List<CbsReturnEquipmentEntity> cbsReturnEquipmentEntity1 = systemService.findHql("from CbsReturnEquipmentEntity where deliverCode = '" + cbsDelinerEntity.getCode() + "'");
                                            if (cbsReturnEquipmentEntity1.size() > 0) {
                                                cbsReturnEquipmentDetailEntity1.setReturnEquipmentId(cbsReturnEquipmentEntity1.get(0));
                                            }
                                            cbsReturnEquipmentDetailEntity1.setTypeId(cbsDeliverEquipmentEntity.getTypeId());
                                            cbsReturnEquipmentDetailEntity1.setTypeDes(cbsDeliverEquipmentEntity.getTypeDes());
                                            cbsReturnEquipmentDetailEntity1.setItemId(cbsDeliverEquipmentEntity.getItemId());
                                            cbsReturnEquipmentDetailEntity1.setItem(cbsDeliverEquipmentEntity.getItem());
                                            cbsReturnEquipmentDetailEntity1.setItemTotal(cbsDeliverEquipmentEntity.getTotal());
                                            cbsReturnEquipmentDetailEntity1.setSn(cbsDeliverEquipmentEntity.getSn());
                                            cbsReturnEquipmentDetailEntity1.setDeliverEquipmentId(cbsDeliverEquipmentEntity);
                                            systemService.save(cbsReturnEquipmentDetailEntity1);
                                        }
                                    }
                                }
                                //创建返件日志
                                CbsHandleLogEntity cbsHandleLogEntity = new CbsHandleLogEntity();
                                cbsHandleLogEntity.setBusinessId(cbsReturnEquipmentEntity.getId());
                                cbsHandleLogEntity.setLogType(4);
                                cbsHandleLogEntity.setDescription("创建返件申请单");
                                cbsHandleLogEntity.setCreateName(user.getRealName());
                                cbsHandleLogEntity.setCreateBy(user.getUserName());
                                systemService.saveOrUpdate(cbsHandleLogEntity);
                            }
                        }
                    }
                } else {
                    System.out.println("ID:" + equipmentIds);
                    int j = 0;
                    CbsDeliverEquipmentEntity cbsDeliverEquipmentEntity = systemService.getEntity(CbsDeliverEquipmentEntity.class, equipmentIds.trim());
                    if (cbsDeliverEquipmentEntity != null) {
                        if (cbsDeliverEquipmentEntity.getIsReturn() != null && cbsDeliverEquipmentEntity.getIsReturn() == 2) {
                            CbsReturnEquipmentEntity cbsReturnEquipmentEntity = getcbsReturnEquipmentEntiy();
                            CbsReturnEquipmentDetailEntity cbsReturnEquipmentDetailEntity1 = getCbsReturnEquipmentDetailEntity();
                            //TODO 创建返件任务
                            Date date1 = new Date();
                            DateFormat format1 = new SimpleDateFormat("yyyyMMdd");
                            String time1 = format1.format(date1);
                            StringBuffer buffer = new StringBuffer();
                            buffer.append("FJ");
                            buffer.append(time1);
                            List<Object[]> objects = systemService.findListbySql("select * from cbs_return_equipment where to_days(create_date) = to_days(now()) ORDER BY `code` desc;");
                            if (objects.size() == 0) {
                                buffer.append("001");
                                string1 = buffer.toString();
                                CbsReturnEquipmentEntity returnEquipmentEntity = null;
                                if (j == 0) {
                                    //判断返件表中是否存在该数据
                                    returnEquipmentEntity = systemService.singleResult("from CbsReturnEquipmentEntity where deliverCode = '" + cbsDelinerEntity.getCode() + "'");
                                    if (returnEquipmentEntity == null) {
                                        cbsReturnEquipmentEntity.setCode(string1);
                                        cbsReturnEquipmentEntity.setSrCode(cbsDelinerEntity.getSrCode());
                                        cbsReturnEquipmentEntity.setCinemaName(cbsDelinerEntity.getCinemaName());
                                        cbsReturnEquipmentEntity.setName(cbsDelinerEntity.getUserName());
                                        cbsReturnEquipmentEntity.setPhone(cbsDelinerEntity.getPhone());
                                        cbsReturnEquipmentEntity.setAddress(cbsDelinerEntity.getAddress());
                                        cbsReturnEquipmentEntity.setSoCode(cbsDelinerEntity.getSoCode());
                                        cbsReturnEquipmentEntity.setDeliverCode(cbsDelinerEntity.getCode());
                                        cbsReturnEquipmentEntity.setCrCode(cbsDelinerEntity.getCrCode());
                                        cbsReturnEquipmentEntity.setEquipmentApplyId(cbsDelinerEntity.getEquipmentApplyId());
                                        cbsReturnEquipmentEntity.setStatus(StaticValue.RETURN_STATUS_Log_Type_1);
                                        cbsReturnEquipmentEntity.setLogistics(cbsDelinerEntity.getReturnLogistics());
                                        cbsReturnEquipmentEntity.setLogisticsCode(cbsDeliverEquipmentEntity.getLogisticsNumber());
                                        cbsReturnEquipmentEntity.setSheng(cbsDelinerEntity.getSheng());
                                        cbsReturnEquipmentEntity.setShi(cbsDelinerEntity.getShi());
                                        systemService.save(cbsReturnEquipmentEntity);
                                        j++;
                                    }
                                }
                                //如果returnEquipmentEntity为NULL就证明 已经执行过返件主表的操作
                                if (returnEquipmentEntity == null) {
                                    List<Map<String, Object>> forJdbc = systemService.findForJdbc("SELECT\n" +
                                            "*\n" +
                                            "FROM\n" +
                                            "\tcbs_return_equipment_detail\n" +
                                            "WHERE\n" +
                                            "\tdeliver_equuipment_id = '" + equipmentIds.trim() + "'\n" +
                                            "AND return_equipment_id = '" + cbsReturnEquipmentEntity.getId() + "'");
                                    if (forJdbc == null || forJdbc.size() == 0) {
                                        List<CbsReturnEquipmentEntity> cbsReturnEquipmentEntity1 = systemService.findHql("from CbsReturnEquipmentEntity where deliverCode = '" + cbsDelinerEntity.getCode() + "'");
                                        if (cbsReturnEquipmentEntity1.size() > 0) {
                                            cbsReturnEquipmentDetailEntity1.setReturnEquipmentId(cbsReturnEquipmentEntity1.get(0));
                                        }
                                        cbsReturnEquipmentDetailEntity1.setTypeId(cbsDeliverEquipmentEntity.getTypeId());
                                        cbsReturnEquipmentDetailEntity1.setTypeDes(cbsDeliverEquipmentEntity.getTypeDes());
                                        cbsReturnEquipmentDetailEntity1.setItemId(cbsDeliverEquipmentEntity.getItemId());
                                        cbsReturnEquipmentDetailEntity1.setItem(cbsDeliverEquipmentEntity.getItem());
                                        cbsReturnEquipmentDetailEntity1.setItemTotal(cbsDeliverEquipmentEntity.getTotal());
                                        cbsReturnEquipmentDetailEntity1.setSn(cbsDeliverEquipmentEntity.getSn());
                                        cbsReturnEquipmentDetailEntity1.setDeliverEquipmentId(cbsDeliverEquipmentEntity);
                                        systemService.save(cbsReturnEquipmentDetailEntity1);
                                    }
                                }

                            } else {
                                String o = (String) objects.get(0)[1];
                                String s = o.substring(10, 13);
                                int i = Integer.parseInt(s);
                                i++;
                                if (i <= 9) {
                                    buffer.append("00" + i);
                                } else if (i >= 10 && i < 100) {
                                    buffer.append("0" + i);
                                } else {
                                    buffer.append(i);
                                }
                                string = buffer.toString();
                                CbsReturnEquipmentEntity returnEquipmentEntity = null;
                                if (j == 0) {
                                    //判断返件表中是否存在该数据
                                    returnEquipmentEntity = systemService.singleResult("from CbsReturnEquipmentEntity where deliverCode = '" + cbsDelinerEntity.getCode() + "'");
                                    if (returnEquipmentEntity == null) {
                                        cbsReturnEquipmentEntity.setCode(string);
                                        cbsReturnEquipmentEntity.setSrCode(cbsDelinerEntity.getSrCode());
                                        cbsReturnEquipmentEntity.setSoCode(cbsDelinerEntity.getSoCode());
                                        cbsReturnEquipmentEntity.setCrCode(cbsDelinerEntity.getCrCode());
                                        cbsReturnEquipmentEntity.setDeliverCode(cbsDelinerEntity.getCode());
                                        cbsReturnEquipmentEntity.setCinemaName(cbsDelinerEntity.getCinemaName());
                                        cbsReturnEquipmentEntity.setName(cbsDelinerEntity.getUserName());
                                        cbsReturnEquipmentEntity.setPhone(cbsDelinerEntity.getPhone());
                                        cbsReturnEquipmentEntity.setAddress(cbsDelinerEntity.getAddress());
                                        cbsReturnEquipmentEntity.setEquipmentApplyId(cbsDelinerEntity.getEquipmentApplyId());
                                        cbsReturnEquipmentEntity.setLogisticsCode(cbsDeliverEquipmentEntity.getLogisticsNumber());
                                        cbsReturnEquipmentEntity.setLogistics(cbsDelinerEntity.getReturnLogistics());
                                        cbsReturnEquipmentEntity.setStatus(StaticValue.RETURN_STATUS_Log_Type_1);
                                        cbsReturnEquipmentEntity.setSheng(cbsDelinerEntity.getSheng());
                                        cbsReturnEquipmentEntity.setShi(cbsDelinerEntity.getShi());
                                        systemService.save(cbsReturnEquipmentEntity);
                                        j++;
                                    }
                                }
                                if (returnEquipmentEntity == null) {
                                    List<Map<String, Object>> forJdbc = systemService.findForJdbc("SELECT\n" +
                                            "*\n" +
                                            "FROM\n" +
                                            "\tcbs_return_equipment_detail\n" +
                                            "WHERE\n" +
                                            "\tdeliver_equuipment_id = '" + equipmentIds.trim() + "'\n" +
                                            "AND return_equipment_id = '" + cbsReturnEquipmentEntity.getId() + "'");
                                    if (forJdbc == null || forJdbc.size() == 0) {
                                        List<CbsReturnEquipmentEntity> cbsReturnEquipmentEntity1 = systemService.findHql("from CbsReturnEquipmentEntity where deliverCode = '" + cbsDelinerEntity.getCode() + "'");
                                        if (cbsReturnEquipmentEntity1.size() > 0) {
                                            cbsReturnEquipmentDetailEntity1.setReturnEquipmentId(cbsReturnEquipmentEntity1.get(0));
                                        }
                                        cbsReturnEquipmentDetailEntity1.setTypeId(cbsDeliverEquipmentEntity.getTypeId());
                                        cbsReturnEquipmentDetailEntity1.setTypeDes(cbsDeliverEquipmentEntity.getTypeDes());
                                        cbsReturnEquipmentDetailEntity1.setItemId(cbsDeliverEquipmentEntity.getItemId());
                                        cbsReturnEquipmentDetailEntity1.setItem(cbsDeliverEquipmentEntity.getItem());
                                        cbsReturnEquipmentDetailEntity1.setItemTotal(cbsDeliverEquipmentEntity.getTotal());
                                        cbsReturnEquipmentDetailEntity1.setSn(cbsDeliverEquipmentEntity.getSn());
                                        cbsReturnEquipmentDetailEntity1.setDeliverEquipmentId(cbsDeliverEquipmentEntity);
                                        systemService.save(cbsReturnEquipmentDetailEntity1);
                                    }
                                }


                            }
                            //创建返件日志
                            CbsHandleLogEntity cbsHandleLogEntity = new CbsHandleLogEntity();
                            cbsHandleLogEntity.setBusinessId(cbsReturnEquipmentEntity.getId());
                            cbsHandleLogEntity.setLogType(4);
                            cbsHandleLogEntity.setDescription("创建返件申请单");
                            cbsHandleLogEntity.setCreateName(user.getRealName());
                            cbsHandleLogEntity.setCreateBy(user.getUserName());
                            systemService.saveOrUpdate(cbsHandleLogEntity);
                        }
                    }
                }
            }

            String emailTitle = "巴可发货通知：<" + cbsDelinerEntity.getCode() + ">-<" + cbsDelinerEntity.getCrCode() + ">-<" + cbsDelinerEntity.getCinemaName() + ">-" + getDateStr(new Date());
            //给物流联系人发送邮件和短信
            this.sendMail(cbsDelinerEntity, user, "wl", emailTitle, "mail_deliver_CSASend.ftl");
            this.sendMsg(cbsDelinerEntity, user, "wl", "message_deliver_CSASend.ftl");
            //给仓库联系人发送邮件和短信
            this.sendMail(cbsDelinerEntity, user, "ck", emailTitle, "mail_deliver_CSASend.ftl");
            this.sendMsg(cbsDelinerEntity, user, "ck", "message_deliver_CSASend.ftl");
            //给原物流发取消邮件
            if (!exLogisticsName.isEmpty() && !exLogisticsName.equals(cbsDelinerEntity.getDeliverLogistics())) {
                TSDepart tsDepart = systemService.singleResult("from TSDepart where departname = '" + exLogisticsName + "' and orgType='logistics' ");
                if (tsDepart != null) {
                    List<ZyContactsEntity> wlContactsList = systemService.findHql("from ZyContactsEntity where belongOrg.id = '" + tsDepart.getId() + "' and state='" + StaticValue.DIC_ENGINEER_STATE_ONJOB + "'");
                    if (wlContactsList != null && wlContactsList.size() > 0) {
                        for (ZyContactsEntity wlContacts : wlContactsList) {
                            if (wlContacts != null && StringUtil.isNotEmpty(wlContacts.getEmail())) {
                                Map<String, Object> paraMap1 = new HashMap<>();
                                paraMap1.put("code", cbsDelinerEntity.getCode());
                                paraMap1.put("qxUserName", cbsDelinerEntity.getFhCSA().getRealName());
                                paraMap1.put("qxUserPhone", cbsDelinerEntity.getFhCSA().getMobilePhone());
                                paraMap1.put("qxReason", "CSA转派");
                                String wlEmail = wlContacts.getEmail();
                                ZyEmailEntity eMail = new ZyEmailEntity();
                                emailTitle = "发货单取消：<" + cbsDelinerEntity.getCode() + ">-<" + cbsDelinerEntity.getCinemaName() + ">-" + getDateStr(new Date());
                                eMail.setEmailSubject(emailTitle);
                                eMail.setEmailReceiver(wlEmail);
                                SendMailUtil.sendFtlMail(eMail, "mail_deliver_CSAQx.ftl", paraMap1, systemService);
                            }
                        }

                    }
                }
            }
            //记录操作日志
            this.addDeliverLog(user.getRealName() + "已发货", cbsDelinerEntity.getId(), 2);

        } catch (Exception e) {
            msg = "发货失败";
            flag = false;
            e.printStackTrace();
            System.err.println("事务出现异常,回滚事务！");
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//此处采用手动回滚
        }
        ajaxJson.setMsg(msg);
        ajaxJson.setSuccess(flag);
        return ajaxJson;
    }

    @Override
    public AjaxJson removeDeliver(HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        boolean flag = true;
        TSUser user = ResourceUtil.getSessionUserName();//获取登录的用户信息
        String deliverId = request.getParameter("id");
        String offReason = request.getParameter("candelDes");//取消原因
        String message = "取消发货成功";
        try {
            CbsDelinerEntity cbsdeliver = systemService.get(CbsDelinerEntity.class, deliverId);
            //取消前的状态
            String status = cbsdeliver.getStatus();
            if (!status.equals(StaticValue.CSAqx)) {
                //修改发货单状态
                cbsdeliver.setStatus(StaticValue.CSAqx);//已取消
                //记录取消人
                cbsdeliver.setQxUser(user);
                //填写取消原因
                cbsdeliver.setOffReason(offReason);
                this.saveOrUpdate(cbsdeliver);

                //查看是否有返件
                List<CbsReturnEquipmentEntity> returnList = systemService.findHql("from CbsReturnEquipmentEntity where deliverCode='" + cbsdeliver.getCode() + "'");
                if (returnList.size() > 0) {
                    for (CbsReturnEquipmentEntity re : returnList) {
                        if (re != null) {
                            re.setStatus("5");
                            systemService.saveOrUpdate(re);

                            //记录操作日志
                            CbsHandleLogEntity cbsHandleLogEntity = new CbsHandleLogEntity();
                            cbsHandleLogEntity.setBusinessId(re.getId());
                            cbsHandleLogEntity.setLogType(4);
                            cbsHandleLogEntity.setDescription("CSA取消返件");
                            cbsHandleLogEntity.setCreateName(user.getRealName());
                            cbsHandleLogEntity.setCreateBy(user.getUserName());
                            systemService.saveOrUpdate(cbsHandleLogEntity);
                        }
                    }
                }

                String serviceLivel = "";
                String serviceType = "";
                String serviceCode = "";
                String qxName = "";
                String qxPhone = "";
                if (cbsdeliver != null && StringUtil.isNotEmpty(cbsdeliver.getCrCode())) {
                    serviceCode = cbsdeliver.getCrCode();
                    List<CbsServiceEntity> serviceList = systemService.findHql("from CbsServiceEntity where code='" + cbsdeliver.getCrCode() + "'");
                    if (serviceList != null && serviceList.size() > 0 && serviceList.get(0) != null) {
                        serviceLivel = serviceList.get(0).getLevel();
                        if (serviceList.get(0).getServiceType2() != null) {
                            serviceType = serviceList.get(0).getServiceType2().getName();
                        }
                    }

                    if (cbsdeliver.getQxUser() != null) {
                        qxName = cbsdeliver.getQxUser().getRealName();
                        qxPhone = cbsdeliver.getQxUser().getMobilePhone();
                    }
                }
                String emailTitle = "发货单取消：<" + serviceLivel + ">-<" + serviceType + ">-<" + serviceCode + ">-<" + cbsdeliver.getCinemaName() + ">-" + getDateStr(new Date());
                //邮件通知审批人
                this.sendMail(cbsdeliver, user, "sp", emailTitle, "mail_deliver_CSAQx.ftl");
                //邮件和短信、app通知申请人
                this.sendMail(cbsdeliver, user, "sq", emailTitle, "mail_deliver_CSAQx.ftl");
                this.sendMsg(cbsdeliver, user, "sq", "message_deliver_CSAQx.ftl");
                /*APP消息*/
                if (cbsdeliver != null) {
                    CbsEquipmentApplyEntity apply = cbsdeliver.getEquipmentApplyId();
                    if (apply != null) {
                        if (StringUtil.isNotEmpty(apply.getCreateBy())) {
                            TSUser applyUser = systemService.singleResult("from TSUser where userName='" + apply.getCreateBy() + "'");
                            if (applyUser != null) {
                                CbsAppMessageEntity appMessage = new CbsAppMessageEntity();
                                String content = "您的发货<" + cbsdeliver.getCode() + ">已取消，取消原因:<" + cbsdeliver.getOffReason() + ">。如有疑问请联系处理人：<" + qxName + "> <" + qxPhone + ">。";
                                appMessage.setContent(content);
                                appMessage.setSender(user.getId());
                                appMessage.setReciver(applyUser.getId());
                                appMessage.setState(StaticValue.DIC_APP_MESSAGE_STATE_UNREAD);
                                systemService.save(appMessage);
                                systemService.addLog("新增APP消息,接受方:" + applyUser.getId() + ",内容:" + content, Globals.Log_Leavel_INFO, Globals.log_type_SEND_EMAIL);
                            }
                        }
                    }
                }
                //物流待发货和物流待签收状态,邮件和短信通知CSA，物流和仓库
                if (status.equals(StaticValue.wldfh) || status.equals(StaticValue.wlyfh)) {
                    //CSA
                    this.sendMail(cbsdeliver, user, "CSA", emailTitle, "mail_deliver_CSAQx.ftl");
                    this.sendMsg(cbsdeliver, user, "CSA", "message_deliver_CSAQx.ftl");
                    //物流
                    this.sendMail(cbsdeliver, user, "wl", emailTitle, "mail_deliver_CSAQx.ftl");
                    this.sendMsg(cbsdeliver, user, "wl", "message_deliver_CSAQx.ftl");
                    //仓库
                    this.sendMail(cbsdeliver, user, "ck", emailTitle, "mail_deliver_CSAQx.ftl");
                    this.sendMsg(cbsdeliver, user, "ck", "message_deliver_CSAQx.ftl");

                }
                //物流已发货状态下取消发货创建一条异常记录
                if (status.equals("6")) {
                    CbsDeliverErrorLogEntity error = new CbsDeliverErrorLogEntity();
                    error.setDescription("'物流已发货'状态下取消发货");
                    error.setCbsDeliverId(cbsdeliver);
                    error.setIsClose(1);
                    systemService.save(error);

                }
                //添加发货单操作日志
                this.addDeliverLog(user.getRealName() + "取消了发货,备注:" + cbsdeliver.getOffReason(), cbsdeliver.getId(), 1);

            } else {
                flag = false;
                message = "发货已经被取消，请刷新页面";
            }

        } catch (Exception e) {
            message = "取消发货失败";
            flag = false;
            e.printStackTrace();
            System.err.println("事务出现异常,回滚事务！");
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//此处采用手动回滚
        }

        j.setMsg(message);
        j.setSuccess(flag);
        return j;
    }

    @Override
    public AjaxJson addQH(HttpServletRequest request) {
        AjaxJson j = new AjaxJson();
        String message = "标记欠货成功";
        boolean flag = true;
        CbsDelinerEntity cbsdeliver = new CbsDelinerEntity();
        //获取登录的用户信息
        TSUser user = ResourceUtil.getSessionUserName();
        String deliverId = request.getParameter("deliverid");
        String eid = request.getParameter("ids");
        JSONArray json = JSONArray.fromObject(eid);
        try {
            if (StringUtil.isNotEmpty(deliverId)) {
                CbsDelinerEntity cbsdeliver1 = systemService.get(CbsDelinerEntity.class, deliverId);
                if (!cbsdeliver1.getStatus().equals(StaticValue.qh) && !cbsdeliver1.getStatus().equals(StaticValue.wlyfh)) {
                    /*
                     * 判断发货单中有几个备件
                     * */
                    List<CbsDeliverEquipmentEntity> deliverEQList = systemService.findHql("from CbsDeliverEquipmentEntity where deliverId='" + deliverId + "'");
                    if (deliverEQList.size() <= 1 || json.size() == deliverEQList.size()) {
                        /*
                         * 如果发货单中只有一个设备或者选中的设备数等于发货单中的备件数
                         * 直接将发货单状态改为欠货中
                         * */
                        cbsdeliver1.setStatus("4");
                        cbsdeliver1.setQHTime(new Date());
                        systemService.saveOrUpdate(cbsdeliver1);
                        this.addDeliverLog(user.getRealName() + "标记欠货", cbsdeliver1.getId(), 1);
                    } else {
                        /*
                         *  如果发货单中的设备数量大于一或者选中的设备数量少于发货单中的备件数量
                         *  将选中的设备放入新创建的发货单中并将新发货单状态设置为欠货中
                         * */
                        // 将旧发货单的数据复制给新的发货单
                        BeanUtils.copyProperties(cbsdeliver1, cbsdeliver);
                        cbsdeliver.setCreateDate(new Date());
                        String uuid = getUuid();
                        cbsdeliver.setId(uuid);
                        cbsdeliver.setStatus("4");//欠货中 修改发货单状态
//                      String ids = request.getParameter("ids");
                        String cbsDeliverCode = this.getDelinerCode();
                        cbsdeliver.setCode(cbsDeliverCode);
                        this.save(cbsdeliver);

                        List<CbsReturnEquipmentDetailEntity> returnDetailList = new ArrayList<>();
                        for (int i = 0; i < json.size(); i++) {
                            String equipmentId = (String) json.get(i);
                            System.out.println("equipmentId:" + equipmentId);

                            CbsDeliverEquipmentEntity cbsDeliverEquipment = systemService.get(CbsDeliverEquipmentEntity.class, equipmentId);
                            System.out.println("cbsDeliverEquipment:" + cbsDeliverEquipment);
                            cbsDeliverEquipment.setDeliverId(cbsdeliver);
                            systemService.saveOrUpdate(cbsDeliverEquipment);

                            //判断标记欠货的设备在标记欠货前是否有返件
                            List<Map<String, Object>> returnDetails = systemService.findForJdbc("select * from cbs_return_equipment_detail where deliver_equuipment_id ='" + cbsDeliverEquipment.getId() + "'");
                            if (returnDetails != null && returnDetails.size() > 0 && returnDetails.get(0) != null) {
                                CbsReturnEquipmentDetailEntity returnEquipmentDetailEntity = systemService.get(CbsReturnEquipmentDetailEntity.class, (String) returnDetails.get(0).get("id"));
                                returnDetailList.add(returnEquipmentDetailEntity);
                            }
                        }
                        //如果标记欠货的备件已有对应的返件，将返件添加到新的返件单中
                        if (returnDetailList != null && returnDetailList.size() > 0) {
                            CbsReturnEquipmentEntity returnEquipment = new CbsReturnEquipmentEntity();
                            String returnCode = getReturnCode();
                            returnEquipment.setSrCode(cbsdeliver.getSrCode());
                            returnEquipment.setSoCode(cbsdeliver.getSoCode());
                            returnEquipment.setDeliverCode(cbsdeliver.getCode());
                            returnEquipment.setCrCode(cbsdeliver.getCrCode());
                            returnEquipment.setEquipmentApplyId(cbsdeliver.getEquipmentApplyId());
                            returnEquipment.setStatus(StaticValue.RETURN_STATUS_Log_Type_1);
                            returnEquipment.setLogistics(cbsdeliver.getReturnLogistics());
                            returnEquipment.setCode(returnCode);
                            returnEquipment.setCinemaName(cbsdeliver.getCinemaName());
                            returnEquipment.setName(cbsdeliver.getUserName());
                            returnEquipment.setPhone(cbsdeliver.getPhone());
                            returnEquipment.setAddress(cbsdeliver.getAddress());
                            systemService.save(returnEquipment);

                            CbsHandleLogEntity cbsHandleLogAdd = new CbsHandleLogEntity();
                            cbsHandleLogAdd.setBusinessId(returnEquipment.getId());
                            cbsHandleLogAdd.setLogType(4);
                            cbsHandleLogAdd.setDescription("创建返件申请单");
                            cbsHandleLogAdd.setCreateName(user.getRealName());
                            cbsHandleLogAdd.setCreateBy(user.getUserName());
                            systemService.saveOrUpdate(cbsHandleLogAdd);

                            for (CbsReturnEquipmentDetailEntity returnDetail : returnDetailList) {
                                if (returnDetail != null) {
                                    returnDetail.setReturnEquipmentId(returnEquipment);
                                    systemService.saveOrUpdate(returnDetail);
                                }
                            }
                        }
                        //记录操作日志
                        this.addDeliverLog(user.getRealName() + "标记欠货", cbsdeliver.getId(), 1);
                    }
                    //判断原发货单对应的返件单中的返件数量
                    if (cbsdeliver1 != null) {
                        checkeCountByDeliver(cbsdeliver1.getCode(), user);
                    }
                } else {
                    flag = false;
                    message = "标记欠货失败,已经是欠货状态，请刷新页面再操作";
                }
            }
        } catch (Exception e) {
            message = "标记欠货失败";
            flag = false;
            e.printStackTrace();
            System.err.println("事务出现异常,回滚事务！");
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//此处采用手动回滚
        }
        j.setSuccess(flag);
        j.setMsg(message);
        return j;
    }

    @Override
    public String getUuid() {
        String uuid = UUID.randomUUID().toString().replaceAll("-", "");
        System.out.println("uuid:" + uuid);
        return uuid;
    }

    @Override
    public String getReturnCode() {
        String code = "";
        Date date = new Date();
        DateFormat format = new SimpleDateFormat("YYYYMMdd");
        String time = format.format(date);
        System.out.println(time);
        StringBuffer buffer = new StringBuffer();
        buffer.append("FJ");
        buffer.append(time);
        List<Object[]> returnEquipmentList = systemService.findListbySql("select * from cbs_return_equipment where to_days(create_date) = to_days(now()) ORDER BY `code` desc;");

        if (returnEquipmentList.size() == 0) {
            buffer.append("001");
            code = buffer.toString();
        } else {
            String o = (String) returnEquipmentList.get(0)[1];
            String s = o.substring(10, 13);
            int l = Integer.parseInt(s);
            l++;
            if (l <= 9) {
                buffer.append("00" + l);
            } else if (l >= 10 && l < 100) {
                buffer.append("0" + l);
            } else {
                buffer.append("" + l);
            }
            code = buffer.toString();
        }

        return code;
    }

    @Override
    public List<Highchart> getDeliverData(String type, String slaType, String startTime, String endTime) {
        List<Highchart> serireData = new ArrayList<>();
        List<Map<String, Object>> logisticsMapList = systemService.findForJdbc("SELECT departname FROM t_s_depart where org_type = ? ", StaticValue.DIC_ORG_TYPE_LOGISTICS);
        //物流商集合
        List<String> diliverLogistics = new ArrayList<>();
        for(Map<String, Object> m : logisticsMapList){
            diliverLogistics.add(m.get("departname").toString());
        }
        diliverLogistics.add("Target");
        diliverLogistics.add("Combine");

        try {
            SimpleDateFormat ymSdf = new SimpleDateFormat("yyyy-MM");
            String begin, end,
                    conditionSql = "";
            if(StringUtil.isNotEmpty(startTime) && StringUtil.isNotEmpty(endTime)){
                //如果按 发货时间 范围查询
                begin = ymSdf.format(DateUtils.date_sdf.parse(startTime));
                end = ymSdf.format(DateUtils.date_sdf.parse(endTime));
                conditionSql = " AND csa_sendtime BETWEEN '"+ startTime +"' AND '" + endTime +"' ";//目前这个方法只用于发货时间的查询 所以只有一句话
            }else{
                //否则默认当年 1月 至 12月
                int year = DateUtils.getYear();
                begin = year + "-01";
                end = year + "-12";
            }
            
            conditionSql += " AND deliver_type in ('1','2','6') ";
            
            List<String> monthBetween = getMonthBetween(begin, end);
            int hours = 0;//SL1和SL2对应的时效限制
            String wlsla = "";
            double targetRate = 0D;
            if ("SL1".equals(slaType)) {
                hours = 24;
                targetRate = 0.75;
                wlsla = "SL1";
            }else if("SL2".equals(slaType)){
                hours = 48;
                targetRate = 0.9;
                wlsla = "SL2";
            }
            DecimalFormat df = new DecimalFormat("0.0000");
            //正常查询
            List<Map<String,Object>> finishCountMapList =
                    systemService.findForJdbc("SELECT CONCAT(deliver_logistics,'-',DATE_FORMAT(csa_sendtime,'%Y-%m')) AS deliverYearMonth, COUNT(*) AS ct FROM cbs_deliver WHERE `status` = 7 "+ conditionSql +" GROUP BY deliver_logistics, DATE_FORMAT(csa_sendtime,'%Y-%m')");
            List<Map<String,Object>> sl1CountMapList =
                    systemService.findForJdbc("select CONCAT(deliver_logistics,'-',DATE_FORMAT(csa_sendtime,'%Y-%m')) AS deliverYearMonth, COUNT(*) AS ct FROM cbs_deliver WHERE TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <= ? AND `status` = 7 "+ conditionSql +" GROUP BY deliver_logistics, DATE_FORMAT(csa_sendtime,'%Y-%m') ", hours);
            List<Map<String,Object>> notSl1CountMapList = new ArrayList<>();
            if(!type.equalsIgnoreCase("gross")){
                notSl1CountMapList =
                        systemService.findForJdbc("select CONCAT(deliver_logistics,'-',DATE_FORMAT(csa_sendtime,'%Y-%m')) AS deliverYearMonth, COUNT(*) AS ct FROM cbs_deliver WHERE ((not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))) AND TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) > ? and wl_SLA <=? and `status` = 7 "+ conditionSql +" GROUP BY deliver_logistics, DATE_FORMAT(csa_sendtime,'%Y-%m') ", hours,wlsla);
            }
            //Combine 查询
            List<Map<String,Object>> combineFinishCountMapList =
                    systemService.findForJdbc("SELECT DATE_FORMAT(csa_sendtime,'%Y-%m') AS yearMonth, COUNT(*) AS ct FROM cbs_deliver WHERE `status` = 7 "+ conditionSql +" GROUP BY DATE_FORMAT(csa_sendtime,'%Y-%m') ");
            List<Map<String,Object>> sl2CountMapList =
                    systemService.findForJdbc("SELECT DATE_FORMAT(csa_sendtime,'%Y-%m') AS yearMonth, COUNT(*) AS ct FROM cbs_deliver WHERE TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) <= ? and `status` = 7 "+ conditionSql +" GROUP BY DATE_FORMAT(csa_sendtime,'%Y-%m') ", hours);
            List<Map<String,Object>> notSl2CountMapList = new ArrayList<>();
            if(!type.equalsIgnoreCase("gross")){
                notSl2CountMapList =
                        systemService.findForJdbc("SELECT DATE_FORMAT(csa_sendtime,'%Y-%m') AS yearMonth, COUNT(*) AS ct FROM cbs_deliver WHERE ((not_qualified = '非物流原因' AND (eta_qunqualified is NULL or eta_qunqualified = '非物流原因')) OR (eta_qunqualified = '非物流原因' AND (not_qualified is NULL or not_qualified = '非物流原因'))) and TIMESTAMPDIFF(HOUR,csa_sendtime,gettime) > ? and wl_SLA <=? and `status` = 7 "+ conditionSql +" GROUP BY DATE_FORMAT(csa_sendtime,'%Y-%m') ", hours,wlsla);
            }


            //map ，key ： 物流商-年月 ，value ： 比例(Target的 key 特殊，不加年月)
            Map<String, Object> dataMap = new HashMap<>();
            for(Map<String,Object> m : finishCountMapList){
                String deliverYearMonth = m.get("deliverYearMonth").toString();
                String ct = m.get("ct").toString(), sl1Count = "", notSl1Count = "";

                //以总计的为基础循环每个指标的集合
                for(Map<String,Object> sl1Map : sl1CountMapList){
                    if(sl1Map.get("deliverYearMonth").toString().equals(deliverYearMonth)){
                        sl1Count = sl1Map.get("ct").toString();
                        break;
                    }
                }
                if(!type.equalsIgnoreCase("gross")){
                    for(Map<String,Object> notSl1Map : notSl1CountMapList){
                        if(notSl1Map.get("deliverYearMonth").toString().equals(deliverYearMonth)){
                            notSl1Count = notSl1Map.get("ct").toString();
                            break;
                        }
                    }
                }
                sl1Count = "".equals(sl1Count) ? "0" : sl1Count;
                notSl1Count = "".equals(notSl1Count) ? "0" : notSl1Count;
                String faultRateStr = "";
                if(type.equalsIgnoreCase("gross")) {
                    faultRateStr = df.format(Double.valueOf(sl1Count)/ Double.valueOf(ct));
                }else{
                    faultRateStr = df.format((Double.valueOf(sl1Count) + Double.valueOf(notSl1Count))/ Double.valueOf(ct));
                }
                dataMap.put(deliverYearMonth, Double.valueOf(faultRateStr));
                dataMap.put("Target", targetRate);
            }
            //Combine
            for(Map<String,Object> m : combineFinishCountMapList){
                String yearMonth = m.get("yearMonth").toString();
                String ct = m.get("ct").toString(), sl2Count = "", notSl2Count = "";

                //以总计的为基础循环每个指标的集合
                for(Map<String,Object> sl1Map : sl2CountMapList){
                    if(sl1Map.get("yearMonth").toString().equals(yearMonth)){
                        sl2Count = sl1Map.get("ct").toString();
                        break;
                    }
                }
                if(!type.equalsIgnoreCase("gross")){
                    for(Map<String,Object> notSl1Map : notSl2CountMapList){
                        if(notSl1Map.get("yearMonth").toString().equals(yearMonth)){
                            notSl2Count = notSl1Map.get("ct").toString();
                            break;
                        }
                    }
                }
                sl2Count = "".equals(sl2Count) ? "0" : sl2Count;
                notSl2Count = "".equals(notSl2Count) ? "0" : notSl2Count;
                String faultRateStr = "";
                if(type.equalsIgnoreCase("gross")) {
                    faultRateStr = df.format(Double.valueOf(sl2Count)/ Double.valueOf(ct));
                }else{
                    faultRateStr = df.format((Double.valueOf(sl2Count) + Double.valueOf(notSl2Count))/ Double.valueOf(ct));
                }
                dataMap.put("Combine-"+yearMonth, Double.valueOf(faultRateStr));
            }
            //物流商做基础
            for(String diliverLogistic : diliverLogistics){
                List<Highchart> highcharts = new ArrayList<>();
                //查询时间范围内的每个月份
                List<Map<String, Object>> mList = new ArrayList<>();
                for(String yearMonth : monthBetween){
                    Map<String, Object> m = new HashMap<>();
                    String key = diliverLogistic + "-" + yearMonth;//组合出 key
                    if(diliverLogistic.equalsIgnoreCase("target")){
                        key = diliverLogistic;
                    }
                    m.put(changeDate(yearMonth), !StringUtil.isNotEmpty(dataMap.get(key)) ? null : dataMap.get(key));
                    mList.add(m);
                }
                Highchart hg = new Highchart();
                hg.setName(diliverLogistic);
                hg.setData(mList);
                highcharts.add(hg);
                serireData.addAll(highcharts);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return serireData;
    }


    //判断发货对应的返件单但是否还有返件，如果没有则取消该返件
    public void checkeCountByDeliver(String deliverCode, TSUser user) {
        List<CbsReturnEquipmentEntity> reList = systemService.findHql("from CbsReturnEquipmentEntity where deliverCode='" + deliverCode + "'");
        if (reList != null && reList.size() > 0) {
            for (CbsReturnEquipmentEntity re : reList) {
                List<Map<String, Object>> rdIds = systemService.findForJdbc("select * from cbs_return_equipment_detail where return_equipment_id ='" + re.getId() + "'");
                if (rdIds == null || rdIds.size() <= 0) {
                    re.setStatus("5");
                    systemService.saveOrUpdate(re);

                    //创建返件日志
                    CbsHandleLogEntity cbsHandleLogEntity = new CbsHandleLogEntity();
                    cbsHandleLogEntity.setBusinessId(re.getId());
                    cbsHandleLogEntity.setLogType(4);
                    cbsHandleLogEntity.setDescription("创建返件申请单");
                    cbsHandleLogEntity.setCreateName(user.getRealName());
                    cbsHandleLogEntity.setCreateBy(user.getUserName());
                    systemService.saveOrUpdate(cbsHandleLogEntity);
                }
            }
        }
    }

    //当前日期转化为MMdd字符转
    public String getDateStr(Date date) {
        SimpleDateFormat df = new SimpleDateFormat("MMdd");
        String dateStr = df.format(date);
        return dateStr;
    }

}